Shorter 'Or' Statement?

[Bigfoot17]

I have the following line working, but it seems that perhaps the line could
be shortened:
If Range("B" & CurRow) = "X" Or Range("B" & CurRow) = "x" Or Range("B" &
CurRow) = "O" Or Range("B" & CurRow) = "o" Then
If I needed to add a few more acceptable characters would I continue adding
more 'or' statements or can I try another method?

Thanks.

 

[Dave Peterson]

select case lcase(activesheet.range("B" & currow).value)
case is = "x","o"
'do something
case else
'do something else
end select

 

[JaimeVasquez]

I have the following line working, but it seems that perhaps the line could
be shortened:
If Range("B" & CurRow) = "X" Or Range("B" & CurRow) = "x" Or Range("B" &
CurRow) = "O" Or Range("B" & CurRow) = "o" Then
If I needed to add a few more acceptable characters would I continue adding
more 'or' statements or can I try another method?

Thanks.

Hi, try with something like this:

If UCase$(Range("B" & CurRow)) Like "[ABCDEFG]" Then MsgBox "ABCDEFG"

HTH

Saludos,
Jaime Vasquez.

 

[Peter T]

With your example you could reduce your Or's if you use UCase.
s = UCase(Range("B" & CurRow))

Also faster to assign the value to a variable rather than reading the cell
multiple times.

Maybe 'Like' might work for you

If Range("B" & CurRow) Like "[OXox]" then
or
If Ucase("B" & CurRow) Like "[OX]" then

Regards,
Peter T

 

[JE McGimpsey]

Small correction:

If UCase(Range("B" & CurRow).Text)) Like "[OX]" Then

or, equivalently:

If UCase(Cells(CurRow, 2).Text)) Like "[OX]" Then

I wonder if the added function overhead makes that form less efficient
than the explicit "[OXox]" version...

 

[Peter T]

Not sure if better to return the .Text vs the .Value property, might depend
on the scenario. Off the top of my head, with a single alpha character in a
cell I can't think what might make them different.

I wonder if the added function overhead makes that form less efficient
than the explicit "[OXox]" version...

Would need to test, as a guess, with a small set of characters probably not
worth using UCase, perhaps a payback comes with a longer set.

Regards,
Peter T

Small correction:

If UCase(Range("B" & CurRow).Text)) Like "[OX]" Then

or, equivalently:

If UCase(Cells(CurRow, 2).Text)) Like "[OX]" Then

I wonder if the added function overhead makes that form less efficient
than the explicit "[OXox]" version...

Maybe 'Like' might work for you

If Range("B" & CurRow) Like "[OXox]" then
or
If Ucase("B" & CurRow) Like "[OX]" then

 

[Peter T]

To touch on a different issue, you should note that every single time you
have Range("B" & CurRow) written, it will be evaluated. Therefore it's
evaluated FOUR times in your statement above. You can save a little time by
doing something like this:

Dim CellValue As String

CellValue = Range("B" & CurRow).Value

If CellValue = "blah" Or CellValue = "Yada" Or ...<etc.>

Indeed reading cells is relatively slow and better to assign it's value to a
variable for repeated use (I also suggested same in previous post).

However it's worth noting that reading a cell's value (or text property)
does not "evaluate" the cell. If you need to do that, and you might if
calculation is set to manual, need either to force a calculation (range,
sheet wb or full) or do -

cellValue = application.evaluate(Range("B" & CurRow).Value)

Regards,
Peter T

 

[Peter T]

Terminology problem then (mine). To me, "evaluate" means to process the
expression. In other words, actually looking up the Value property of
Range("B" & CurRow) is evaluation. But I'm a VB6 guy, and I guess the word
has a different meaning under Excel.

Perhaps I was being a bit pedantic and "evaluate" can be taken to mean
different things. But with respect to cells there can be a difference
between it's current Value property and it's potential Evaluated formula
result where calculation has not been updated.

However the example I was incorrect

should have read

cellValue = application.evaluate(Range("B" & CurRow).Formula)

Regards,
Peter T

 

[JE McGimpsey]

Only reason I use .Text vs .Value is that the .Text property returns a
String, which is the proper input argument type for the Like operator,
whereas .Value returns a Variant which needs to be (either explicitly or
implicitly) converted.

OTOH, I don't know what I'd do with all those saved nanoseconds...