Search bar in Excel

Question

I'm currently working on a Excel project that needs a search bar in it. I have just started to work with Excel so I'm really a noob.

I have a TextBox where I can input the text. And a button next to it so i can execute the search. The search should search from RANGE("A:E"). And i need it to highlight the result.

Example:

I have a string "hello" in cell ("C12"), and if I type in my TextBox hello and press the button i want it to go to that cell and select it.

I have tried everything, searched the web and I can't find nothing that could fit.

The only one that could work is the VLOOKUP but I need it in more that 1 column, so that will not work. This should work like the ctr+F thing but I can't find nothing that looks like that.

The only code that could work is the one where I would go manually down for every cell and check it for the string:

Sub Button1_Click()
Dim Column, Row As Integer
Dim Search As String
String = TextBox.Text
For Column = 1 To 5
    For Row = 1 To 1048576
        If Cells(Row, Column).Value = Search Then
            Cells(Row, Column).Select
        End If
    Next
Next

End Sub

But this code gives an overflow error. If you could please help me I would love it.

Answer 1

Something like this should work for you. It uses a range.find loop instead of a brute force loop so it is much more efficient and will avoid the overflow error

Private Sub Button1_Click()

    Dim rngFound As Range
    Dim rngSelect As Range
    Dim strFind As String
    Dim strFirst As String

    If Len(Trim(Me.TextBox.Text)) = 0 Then
        Me.TextBox.SetFocus
        MsgBox "Must provide text to search for.", , "No Search Text"
        Exit Sub
    End If

    strFind = Me.TextBox.Text
    Set rngFound = Range("A:E").Find(strFind, Cells(Rows.Count, "E"), xlValues, xlPart, MatchCase:=False)

    If rngFound Is Nothing Then
       MsgBox "No matches found for [" & strFind & "]", , "No Matches"
       Exit Sub
    Else
        strFirst = rngFound.Address
        Set rngSelect = rngFound
        Do
            Set rngSelect = Union(rngSelect, rngFound)
            Set rngFound = Range("A:E").FindNext(rngFound)
        Loop While rngFound.Address <> strFirst
    End If

    rngSelect.Select

End Sub