IF statement to check against list of values

[Dan]

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"

[Nick Hodge]

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" <[email protected]> wrote in message
news:[email protected]...
> 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"

[Dan]

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

[tony h]

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