Find Method Does Not Work

[msquared99]

I have a UserForm, I enter a ID number into a TextBox called tbCOID. I am trying to use the Find method to look for the value in tbCOID and have it populate other TextBoxes. The data I'm trying to retrieve is stored in Sheet3 of the Workbook that has the macro.


The code only returns the values in row 2. It is as if that is the only row the code looks at.


Any suggestions?

Here is the code:

[pre]

Private Sub tbCOID_Exit(ByVal Cancel As MSForms.ReturnBoolean)

'Looks for COID in Sheet3 and auto populates several textboxes
Dim rng As Range, SearchResult As Range
Dim cel As String

Me.tbCOID = Format(Me.tbCOID, "0000")

Set rng = Worksheets("Sheet3").Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
cel = tbCOID.Value

Set SearchResult = rng.Find(what:=cel, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
If Not SearchResult Is Nothing Then
'Found it!

'Populate TextBoxes with found values from Sheet3
frmSales.tbName.Value = SearchResult.Offset(0, 1).Value
frmSales.tbRep.Value = SearchResult.Offset(0, 2).Value
frmSales.tbEECount.Value = SearchResult.Offset(0, 5).Value
frmSales.tbHourlyEE.Value = SearchResult.Offset(0, 6).Value
frmSales.tbInput.Value = SearchResult.Offset(0, 3).Value

tbHourlyPer.Value = Format(tbHourlyEE.Value / tbEECount.Value, "0.0%")

Else
'Did not find it.
MsgBox "COID Not Found!"
End If

End Sub

[/pre]

 

[Luke M]

Just to clarify, when you say it's only returning data from row 2, if your data looks like this:

[pre]

ID	Data1	Data2	Data3	Data4
1001	1	2	4	1
1002	4	2	4	11
1003	7	12	1	18
1004	3	1	19	8
1005	10	1	1	26

[/pre]
When you input "1002" into your user form, you're getting the data for 1001 returned (1, 2, 4, 1)?

 

[Debraj]

Hi msquared99,


In addition to Luke, can you please also clarify if Me (current user form where tbCOID and code exists) and frmSales are two different UserForm..

If they are same, then no need to use frmsales.tb.. and if they are different, they you need to add frmSales.show . otherwise everything looks fine for me..

we will discuss after your confirmation..

Regards,

Deb

 

[msquared99]

Luke,


If I input 1002 my message box returns "COID Not Found!" If I input 1001 the TextBoxes are populated properly. It is like only row 2 is selected instead of looking all of column A of Sheet3.


Deb,


They are the same tbCOID is in frmSales and the rest of the TextBoxes.


I have frmSales.Show in a separate Module.

 

[Debraj]

Ok Mike,


I guess Problem is in below..

Set rng = Worksheets("Sheet3").Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)

When you are trying to find last row of Sheet3, you are not giving him from which area [code]Cells(Rows.count,"A") need to read. If your active sheet is "Sheet3" and frmSales is shows, it will work. But if active sheet is not Sheet3 then, for the Range, it will set sheet3.Range A2 to A - last row in Current ActiveSheet..


Change this line to

Set rng = Worksheets("Sheet3").Range("A2:A" & Worksheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row)

or

[pre]With Worksheets("Sheet3&#34
Set rng = .Range("A2:A" & .Cells(Rows.Count, "A&#34.End(xlUp).Row)
End With[/code][/pre]
Not good in explanation but hope it will Work..


Regards,

Deb

 

[msquared99]

Deb,


It works perfect!


I had the second suggestion but was using it in the wrong place.


Thanks so much for everyone's help!


Mike