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

Do you want to get the first match or possible matches ??

Gary's Student
– Gary's Student

2016-02-21 16:33:44 +00:00
Commented Feb 21, 2016 at 16:33 — Gary's Student
– Gary's Student, Commented Feb 21, 2016 at 16:33

Gary's Student · Accepted Answer · 2016-02-21 16:43:32Z

6

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

answered Feb 21, 2016 at 16:43

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dani jinji Over a year ago

Hi thanks for the answer, works allmost perfect, though when I do two wildcrads like this: "123*5*7" it may also give me stuff like this: "1233447" - notice that it gave a result that shouldn't appear @Gary's

Paul Russell Over a year ago

It should be noted that the two examples given above do not always behave the same. In the Find example, the first cell to be searched is the cell AFTER the first cell, i.e. A2. If more than one value in the range matches the search text, and one of those values is in cell A1, the two examples will behave differently, with the Find returning the address of the second instance, and the loop returning the address of the first instance (A1).

Collectives™ on Stack Overflow

vba excel - find string wildcard

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related