CODEWITHSUNDEEP
excelvba
user1986119
user1986119

Reputation: 33

How to Write OR statement in excel VBA with multiple conditions

How to Write OR statement in excel VBA with multiple conditions. I tried it as 

For i = 1 To 30
a = Sheets("Interface").Range("A" & i).Value
If (Sheets("Interface").Range("A" & i).Value Like "Street*name") Or _
   (UCase(Sheets("Interface").Range("A" & i).Value) Like "Street*address") Or _
   (UCase(Sheets("Interface").Range("A" & i).Value) Like "address") Then
'If a Like "*Street*address*" Then
Sheets("Interface").Range("B" & i).Value = "STREETNAME"
End If
Next i

I tried it with simple street*address statement(comment code) but it didn't get into the true code.whats wrong with this code.

Upvotes: 2

Views: 29835

Answers (2)

LimaNightHawk
LimaNightHawk

Reputation: 7093

The problem with VB/VBA is that if you write compound Or statements like this, VB will evaluate every condition, even if the first condition is found to be True. It won't short-circuit.

It's a bit unconventional, but if you want a short-circuit method, you can use a Select Case, like this:

Select Case True
    Case a Like b, _
         a Like c, _
         a Like d, _

        Debug.Print "True"

    Case Else
        Debug.Print "False"

End Select

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166825

Something like this:

For i = 1 To 30

    a = UCase(Sheets("Interface").Range("A" & i).Value)

    If a Like "STREET*NAME" Or a Like "STREET*ADDRESS" Or _
                               a Like "ADDRESS" Then

        Sheets("Interface").Range("B" & i).Value = "STREETNAME"

    End If

Next i

Upvotes: 4

Related Questions