Testing equality to a bunch of values for conditional statement

[Legacy 96851]

Just a general question. If I have something like:

If .Cells(1, ii) = "Comments" Or .Cells(1, ii) = "ABC" Or .Cells(1, ii) = "CDE" _
                                Or .Cells(1, ii) = "LEE" Or .Cells(1, ii) = "LSE" Then

is there a way to shorten it? I sort of assumed there was, but can't figure it out. Thanks!

 

[Jonmo1]

Sure, try this..

MyVals = Array("Comments","ABC","CDE","LEE","LSE")
x = Apppliction.Match(.Cells(1,ii),MyVals,0)
If Not IsError(x) Then...

Hope this helps..

 

[Oaktree]

Try using a Select Case Statement

Select Case .Cells(1,ii)
Case "Comments","ABC",...,"LSE"
'do something
end select

 

[Andrew Poulsom]

Another way:

Select Case .Cells(1, ii).Value
    Case "Comments", "ABC", "CDE", "LEE", "LSE"
'        Do stuff
End Select

 

[lenze]

Code Select Case .cells(1,ii)
Case "Comments","ABC","CDE","LEE","LSE"
 'some code
Case Else:
End Select

lenze

Edit: Darn I'm slow

 

[Legacy 96851]

Ah, okay, thanks a lot. I figured there was probably an array related solution. And the "case" solution I guess is sort of like a switch in Java, which was what I was looking for (I just started learning VBA, and Java is the only other programming language I know; I can't help but constantly connect the two).

 

[Andrew Poulsom]

lenze

Edit: Darn I'm slow

Even slower than me!

 

[Jonmo1]

C'mon guys, it's not a race...

And if it was, I won...

 

[Legacy 96851]

C'mon guys, it's not a race...

And if it was, I won...

And you had the most unique answer. I guess you just plain won the thread.

...Though in all fairness, I used the other guys' answer.