IF statement to check against list of values
 

I'm looking to right an if statment in VBA that accomplishes this;

IF var1 IN ("XXX", "YYY", "ZZZ") THEN
DoThing1
ELSE
DoThing2
Endif

I tried it as above, and it didn't like the use of "IN"

IF statement to check against list of values
 

Dan

IN is a SQL command, try a select case statement

Sub SelectCase()
Select Case var1
Case Is = "XXX"
MsgBox "XXX"
Case Is = "YYY"
MsgBox "YYY"
Case Is = "ZZZ"
MsgBox "ZZZ"
Case Else
MsgBox "Something Else"
End Select
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk


"Dan" wrote in message
...
I'm looking to right an if statment in VBA that accomplishes this;

IF var1 IN ("XXX", "YYY", "ZZZ") THEN
DoThing1
ELSE
DoThing2
Endif

I tried it as above, and it didn't like the use of "IN"

IF statement to check against list of values
 

Nick, thanks for the response. I think in the example you give, you have
different message boxes coming up for the different options (XXx, YYY, ZZZ).
What I want is it to do the same thing if VAR1 equals either XXX, YYY or ZZZ,
and something else if VAR1 is anything but XXX, YYY, or ZZZ.

"Nick Hodge" wrote:

Dan

IN is a SQL command, try a select case statement

Sub SelectCase()
Select Case var1
Case Is = "XXX"
MsgBox "XXX"
Case Is = "YYY"
MsgBox "YYY"
Case Is = "ZZZ"
MsgBox "ZZZ"
Case Else
MsgBox "Something Else"
End Select
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk

IF statement to check against list of values
 

1. You could use a delimited list and then

if instr(1, "!A!B!C!" , "!" & myvar & "!") < 0 then etc

2. use

Select Case myvar
Case "A", "B", "C"
'do my stuff
End Select


3 create your own function to do it such as

Function myIN(strSearch, ParamArray strIN()) As Boolean
Dim v As Variant

myIN = False

For Each v In strIN()
If strSearch = v Then
myIN = True
Exit For
End If
Next
End Function

if myin(myvar,"B","C","A") then

Regards


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=507445