Check if value in list

[John]

I have a column in a hidden worksheet that has a bunch of values
vertically downward. Say I have a variable that stores a single
value. I want to see if that value exists in that column in another
worksheet. How would I do this in VBA?

So if Worksheet "Sheet2" has in Column "G" the values vertically JOE,
SAM, PETE, MIKE, BILL, SUE (this list could change, grow, shrink) and
I have a variable that has "JIM" in it, I want to see if it's in
Column "G" returning whether it is or not. Kind of like

If UCase(VariableName) in "JOE,SAM,PETE,MIKE,BILL,SUE" then
....

Thanks.

JR

 

[Tom Ogilvy]

Dim res as Variant
res = Application.Match(VariableName,worksheets("Sheet2").Range("G:G"),0)
if not iserror(res) then
msgbox "Match found"
Else
msgbox "Match not found
End if

You can also use find

set rng = Worksheets("Sheet2").Range("G:G").Find( _
What:=VariableName)
if not rng is nothing then
msgbox "Match found"
else
msgbox "Match Not found"
End if

 

[John]

Thanks Tom. The samples below work good. If I wanted to use the Find
method, not sure if using Application. to use macro functionality is
good or bad, however is there a way to force a find match on the enter
word? So if one of the cells in "G" have "sam" and the VariableName
is "sa", I do not want it to match. It should only match if there is
an exact match. Won't really be an issue for what I'm doing, however
like to make sure things do not run astray for whatever reason.
Thanks.

JR

 

[Leo Heuser]

John

If I have understood you correctly, then
add the following to Tom's code:

set rng = Worksheets("Sheet2").Range("G:G").Find( _
What:=VariableName, LookIn:=XlValues, _
LookAt:=xlWhole, MatchCase:=False)
if not rng is nothing then
msgbox "Match found"
else
msgbox "Match Not found"
End if