vba excel - find string wildcard

Question

I am doing a simple search engine in excel and I want to make some wildcards, for example:

I have a cell where the user input the search term (only numbers) which should look like this: "123456". then, I have another workbook, where I search for the "123456" exactly. this I managed to do.

however, how can I make wildcards? for example, I want the user to be able to search for: "123?56" and I will give him the results of: "123456", "123356", "123556" etc.

this is how I look for the exact match:

set rFound = wks.UserRange.Find(strToSearch, LookIn:=xlValues, lookat:=xlwhole, MatchCase:=False)

any ideas?

thank you

Answer 1

You can use a wildcard either in a loop or with Find:

Sub dural2()
    MsgBox Range("A1:A10").Find(What:="123*56", After:=Range("A1")).Row
End Sub

or in a loop with Like:

Sub dural()
    For Each r In Range("A1:A10")
        If r.Value Like "123*56" Then
            MsgBox r.Address
        End If
    Next r
End Sub