Sleeper: Search Function Problem

[gibbo1715]

All

Im using the function below to search a sheet


Function fnFind(strFind, Optional sh) As Range
If IsMissing(sh) Then Set sh = ActiveSheet
On Error Resume Next
Set fnFind = sh.Cells.Find(What:=strFind, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End Function
Sub TestfnFind()
Dim SearchFor As Range
Set SearchFor = fnFind(InputBox("What do you want to search for", "Search"), Sheet1)
If SearchFor Is Nothing Then
MsgBox ("Sorry Not Found")
Else
Sheets("Sheet1").Select ' Better way of coding this line?
SearchFor.Select
End If
End Sub

To get my results i am using the line Sheets("Sheet1").Select and was wondering if there was a better way of doing this?

Cheers

Gibbo

[Bob Phillips]

Why are you selecting it. The SearchFor object is pointing at the found cell (or nothing if not found), so you can work on it immediately without selecting.

[gibbo1715]

I need to select as I am using the active cell (And offsets) to populate the text boxes on my userform, and to be able to navigate records from there

[royUK]

As XLD says you dont need to select it to use it to populate the TextBoxes, you can put the value into the TextBox likethis


Me.TextBox1.Value=Searchfor.Value

[gibbo1715]

Thanks for your reply

I need to also populate other text boxes

e.g the value of textbox 2 would be Cell.Offset(0,1).value

I assume Me.TextBox2.Value=Searchfor.Offset(0,1).Value wont work thats why i was selecting the cell

Ok, Just tried that out of interest and suprissed to find it does work, Thats Cool

I still have a problem though that I have navigation buttons on my userform, so how would i select the next and previous records etc unless i select the cell?

[Bob Phillips]

Thanks for your reply

I need to also populate other text boxes

e.g the value of textbox 2 would be Cell.Offset(0,1).value

I assume Me.TextBox2.Value=Searchfor.Offset(0,1).Value wont work thats why i was selecting the cell

Never assume. Try it http://vbaexpress.com/forum/images/smilies/001.gif