Sleeper: Search Function Problem

All

Im using the function below to search a sheet

Code:

---

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

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.

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

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

Code:

---

Me.TextBox1.Value=Searchfor.Value

---

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?

Quote:

---

Originally Posted by 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

---

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