Wild Card for partial matches.
 

I have a column with multiple numbers in them,
example of one cell 0502,0399

I want to be able to find partial matches like just the 0502. right now I
have this..

For Each c In MyRange
If c = "*0502*" Then
MsgBox "yes"
End If
Next c

I'm trying to use the * as a wild card but its looking it up as if I want to
find the * as well as the numbers. Any Ideas?

Wild Card for partial matches.
 

In regular Excel, the Edit/Find command accepts ? and * as wildcards by
default.

In VBA, look up the LIKE operator.

--

--Andy Smith



"Kevin" wrote:

I have a column with multiple numbers in them,
example of one cell 0502,0399

I want to be able to find partial matches like just the 0502. right now I
have this..

For Each c In MyRange
If c = "*0502*" Then
MsgBox "yes"
End If
Next c

I'm trying to use the * as a wild card but its looking it up as if I want to
find the * as well as the numbers. Any Ideas?

Wild Card for partial matches.
 

Other possibilities:

- Use Application.WorksheetFunction to use the FIND or SEARCH function

- Use the Find/FindNext/Findprevious method of the Range object

- Use the VBA built-in function Instr

--

--Andy Smith



"Kevin" wrote:

I have a column with multiple numbers in them,
example of one cell 0502,0399

I want to be able to find partial matches like just the 0502. right now I
have this..

For Each c In MyRange
If c = "*0502*" Then
MsgBox "yes"
End If
Next c

I'm trying to use the * as a wild card but its looking it up as if I want to
find the * as well as the numbers. Any Ideas?

Wild Card for partial matches.
 

sub FindData()
Set rng = Selection.Find(What:="0502", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End Sub

the LookAt:=xlPart lets if find the substring.

If you want to find all instances, try applying an autofilter using custom
and select Contains and specify 0502 of look in Excel VBA help at the
FindNext method sample code.

--
Regards,
Tom Ogilvy


"Kevin" wrote:

I have a column with multiple numbers in them,
example of one cell 0502,0399

I want to be able to find partial matches like just the 0502. right now I
have this..

For Each c In MyRange
If c = "*0502*" Then
MsgBox "yes"
End If
Next c

I'm trying to use the * as a wild card but its looking it up as if I want to
find the * as well as the numbers. Any Ideas?

Wild Card for partial matches.
 

Like worked for me. thanks a bunch for your help.

"Andy Smith" wrote:

In regular Excel, the Edit/Find command accepts ? and * as wildcards by
default.

In VBA, look up the LIKE operator.

--

--Andy Smith



"Kevin" wrote:

I have a column with multiple numbers in them,
example of one cell 0502,0399

I want to be able to find partial matches like just the 0502. right now I
have this..

For Each c In MyRange
If c = "*0502*" Then
MsgBox "yes"
End If
Next c

I'm trying to use the * as a wild card but its looking it up as if I want to
find the * as well as the numbers. Any Ideas?