View Full Version : match variable with list

01-10-2009, 06:55 AM
i have a variable called cellvalue that i want to match with a list.

my "betaversion" i use today is :
if cellvalue like xx or cellvalue like xy or cellvalue like xz... and so on..
as you might understand this is not a good way of doing it.

if i put all the values in a sheet can i match them without making a loop.
i understand that i can do like:

while x < LastRow
if cellvalue Like Sheet("List").Range("A" & x).Value Then
...do stuff...
end if
x= x+1

but loops is not very efficient.
what could i do instead?

Bob Phillips
01-10-2009, 07:56 AM
I think this is what you mean

If Not IsError(Application.Match(cellvalue, Sheet("List").Range("A1").Resize(LastRow),0)) Then
'... do stuff...
End If

01-10-2009, 08:39 AM
its very close to working.

the list im matching agains contains something like:


and cellvalue is complete IPs.
the way it is now it only does exact matches. if i change the 0 to a 1 it seems it matches every smal part of the value.

i want it to do wildcardmatch but not completely wild.