There has to be a better way... If/or statements

[rjperket]

Hi all,

I am having trouble with one part of a piece of code where I want to say if the value in a certain cell is equal to one of multiple values and I think there must be a better way to do it. Right now I have a ridiculous number of or statements and it looks something like this:

            If ECpresent.Offset(1, -14).Value = "B01" _
            Or ECpresent.Offset(1, -14).Value = "B02" _
            Or ECpresent.Offset(1, -14).Value = "B03" _
            Or ECpresent.Offset(1, -14).Value = "B04" _
            Or ECpresent.Offset(1, -14).Value = "B05" _
            Or ECpresent.Offset(1, -14).Value = "B06" _
            Or ECpresent.Offset(1, -14).Value = "B07" _
            Or ECpresent.Offset(1, -14).Value = "B08" _
            Or ECpresent.Offset(1, -14).Value = "B09" _
            Or ECpresent.Offset(1, -14).Value = "B10" Then

Anyone have any suggestions?

Can I use a wildcard in some way like "B*"?


Thanks in advance!

Ryan

 

[jamesthfc]

Use Select Case and you can use B01 to B10

 

[Jonmo1]

Sure, you can do

If Left(cell,1) = "B" Then

or

If cell like "B*" Then

 

[JLGWhiz]

Another way of saying the same thing:

Code:
For Each c In Range("B1:B10")
If c.Value = ECpresent.Offset(1, -14).Value Then
'Do something or don't do something
Exit For
End If
Next
Code:

 

[Jonmo1]

How is this the same thing?
The B01 B02 B03 etc in the original post are just text strings, not cell references.

Another way of saying the same thing:

Code:
For Each c In Range("B1:B10")
If c.Value = ECpresent.Offset(1, -14).Value Then
'Do something or don't do something
Exit For
End If
Next
Code:

 

[JLGWhiz]

How is this the same thing?
The B01 B02 B03 etc in the original post are just text strings, not cell references.

That is a real Senior Moment, Jonmo1. Don't know what I was thinking.

 

[rjperket]

Thanks everyone for your speedy replies!

I used this method just because I was most familiar with it:

If Left(ECpresent.Offset(1, -14), 1) = "B" Then

Thanks again!