Excel VBA wildcard search

Question

I currently have an Excel file with 1 column and many rows. The column holds a first name, last name, and possibly a middle name (EXAMPLE: John Abe Smith). I am writing a macro that has 1 textbox and 1 button. In the excel sheet I have a couple names: ``` Column A -------- John Abe Smith Cindy Troll Bee Randy Row Joe Jumbo Katie Kool Kat ``` I want to write a macro that when I type something in the textbox and click the button, it will look in this column for the name. If it finds it, then just say "found" in a message box. I want to use the wildcard "*" when searching the names, but I do not know how. I currently have some code like this, but the wildcard does not work: ``` Private Sub search_Click() For firstloop = 3 To 10 If Range("G" & firstloop).Text = name.Text & "*" Then MsgBox "Found!" Exit Sub Else MsgBox "NOT FOUND" End If Next End Sub ``` For example, let's say I type in "Troll" in the text box and I click the button. I want the loop to go through the column to find anythin…

Accepted Answer

You can use `Like` operator (case-sensitive):

```
Private Sub search_Click()
    For firstloop = 3 To 10
        If Range("G" & firstloop).Text Like name.Text & "*" Then
            MsgBox "Found!"
            Exit Sub
        Else
            MsgBox "NOT FOUND"
        End If
    Next
End Sub
```

for case-insensitive search use:

```
If UCase(Range("G" & firstloop).Text) Like UCase(name.Text) & "*" Then
```

Also if you want to determine whether cell contains text (not only starts with text), you can use (case-sensitive):

```
If InStr(1, Range("G" & firstloop).Text, name.Text) > 0 Then
```

or (case-insensitive)

```
If InStr(1, Range("G" & firstloop).Text, name.Text, vbTextCompare) > 0 Then
```

UPD:

If the point only to show `msgbox`, then I'd suggest to use `Application.Match`:

```
Private Sub search_Click()
    If Not IsError(Application.Match("abc" & "*", Range("G3:G10"), 0)) Then
        MsgBox "Found!"
    Else
        MsgBox "NOT FOUND"
    End If
End Sub
```

Excel VBA wildcard search

Solution

Problem