Searches using a textbox for input

[dpaton05]

Sub searchvba()

' Find the name in the range A9:A1000
Dim rgFound As Range
Set rgFound = Range("A9:A1000").Find("Bob")
rgFound.Select

End Sub

I have code here that searches in the range for Bob. How do I change it so it searches for the text in a text box instead? The name of the textbox is txtname.

Also, how could I change it again to search for the text in a cell?

 

[LegendaryAxe]

Try this:

Sub searchvba()

Dim tbText As String
Dim rgFound As Range

tbText = ActiveSheet.txtname.Text
Set rgFound = Range("A1:A1000").Find(tbText)
rgFound.Select

End Sub

 

[dpaton05]

Thanks for the reply. I now have this:

Sub searchvba()
' Find the name in the range A9:A1000
Dim rgFound As Range
Dim tbText As String

tbText = ActiveSheet.TextBox1.Text
Set rgFound = Range("A9:A1000").Find(tbText)
rgFound.Select


End Sub

..but when I try and run it, I get an error: Object variable or with block variable not set.

 

[dpaton05]

It needs to be able to do partial matches as well as full matches.

 

[LegendaryAxe]

You'll get that if the string isn't found. Try this to account for that:

Edit: Adjusted to make it partial match.

Sub Test()

Dim tbText As String
Dim rgFound As Range

tbText = ActiveSheet.TextBox1.Text
Set rgFound = Range("A1:A1000").Find(What:=tbText, LookAt:=xlPart)
If Not rgFound Is Nothing Then
    rgFound.Select
Else
    MsgBox (tbText & " was not found.")
End If
End Sub

 

[dpaton05]

Thanks for that code, it works great! It just needs to be able to do partial matches too, such as just a first or last name or even wrong spelling. Could you help me with that, please?

 

[LegendaryAxe]

Thanks for that code, it works great! It just needs to be able to do partial matches too, such as just a first or last name or even wrong spelling. Could you help me with that, please?

No problem! Glad to help.

It's not going to cover wrong spelling, but see the edit I made to the previous post. So if you have "Bo", for example, in the textbox, the macro should find "Bob."

 

[dpaton05]

.

 

[dpaton05]

That code you edited works great too! Could you add something so that it allows you to cycle through the results if there are more than 1?

 

[LegendaryAxe]

I'm sure someone could improve on this, but it should work:

Option Explicit

Sub Test()

    Dim tbText As String
    Dim rgFound As Range
    Dim firstAddress As String

    tbText = ActiveSheet.TextBox1.Text

    Set rgFound = Range("A1:A1000").Find(What:=tbText, LookAt:=xlPart)
    
    If Not rgFound Is Nothing Then
        firstAddress = rgFound.Address
        Do
            rgFound.Select
            If MsgBox("Go to next match?", vbYesNo) = vbNo Then
                Exit Sub
            End If
            Set rgFound = Range("A1:A1000").Find(What:=tbText, LookAt:=xlPart, After:=rgFound)
        Loop While Not rgFound Is Nothing And rgFound.Address <> firstAddress
        MsgBox "All matches found."
    Else
        MsgBox tbText & " was not found."
    End If

End Sub