PDA

View Full Version : if..............in



philfer
01-22-2011, 07:51 AM
Hi, Is there a way in Excel VBA to do somthing like :- If myVariable In ["eggs", "ham", "potato"] or do you have to have lots of "OR" statements Thanks Phil

Bob Phillips
01-22-2011, 10:31 AM
Dim myArray As Variant
Dim myVariable As String
Dim aryIdx As Long

myArray = Array("eggs", "ham", "potato")
myVariable = "eggs"

On Error Resume Next
aryIdx = Application.Match(myVariable, myArray, 0)
On Error GoTo 0

If aryIdx > 0 Then

MsgBox "Found in item " & aryIdx
End If

mikerickson
01-22-2011, 06:30 PM
Another approach might be

Select Case myVariable
Case "pear", "apple", "peach"
MsgBox "vegetable"

Case "steak", "bacon", "sausage"
MsgBox "meat"

End Selectxld's method is better if you only want "is it in the bucket, True/False". Select Case would be used if the question is "which bucket is it in"?