CODEWITHSUNDEEP
vbaif-statementmultiple-conditions
gonzalloe
gonzalloe

Reputation: 313

I use OR to form a multiple condition IF ELSE statement on VBA, it's not working

Dim cat As Integer
For cat = 2 To last
    Range("AB" & cat).Select

    If Selection.Value = " " Then
        ActiveCell.Offset(0, -2).Value = "-"
        ActiveCell.Offset(0, -1).Value = "-"

    ElseIf Selection.Value = "Address in local wording" Then
        ActiveCell.Offset(0, -2).Value = "Customer"
        ActiveCell.Offset(0, -1).Value = "Incomplete information or awaiting more info from customer"

    ElseIf (Selection.Value = "hold to console" Or "Hold to console" Or "Allocated 14/12 and ship next day") Then
        ActiveCell.Offset(0, -2).Value = "Depot"
        ActiveCell.Offset(0, -1).Value = "Allotment delay"

    ElseIf (Selection.Value = "Backorder" Or "backorder" Or "Back order" Or "back order") Then
        ActiveCell.Offset(0, -2).Value = "Inventory"
        ActiveCell.Offset(0, -1).Value = "Material not available causing backorder"

    End If        
Next cat

The result I get is when Selection.Value is empty, "-" , "-" and the rest all show "Depot" , "Allotment delay" only.

What's wrong with this code ?

Upvotes: 3

Views: 583

Answers (2)

Shai Rado
Shai Rado

Reputation: 33672

Using the line below is incorrect: 

ElseIf (Selection.Value = "hold to console" Or "Hold to console" Or "Allocated 14/12 and ship next day") Then

You need to add Selection.Value = before each condition, see line below:

ElseIf Selection.Value = "hold to console" Or Selection.Value = "Hold to console" Or Selection.Value = "Allocated 14/12 and ship next day" Then

Note: the same applies to all other ElseIfs you have.

Edit 1

However,I would suggest to use the code below. Your code is "screaming" for Select Case. Also, there is no need to Range("AB" & cat).Select and later use ActiveCell, instead you could just use fully qualifed Range.

Code

Dim cat As Long

For cat = 2 To last
    Select Case Range("AB" & cat).Value
        Case " "
            Range("AB" & cat).Offset(0, -2).Value = "-"
            Range("AB" & cat).Offset(0, -1).Value = "-"

        Case "Address in local wording"
            Range("AB" & cat).Offset(0, -2).Value = "Customer"
            Range("AB" & cat).Offset(0, -1).Value = "Incomplete information or awaiting more info from customer"

        Case "hold to console", "Hold to console", "Allocated 14/12 and ship next day"
            Range("AB" & cat).Offset(0, -2).Value = "Depot"
            Range("AB" & cat).Offset(0, -1).Value = "Allotment delay"

        Case "Backorder", "backorder", "Back order", "back order"
            Range("AB" & cat).Offset(0, -2).Value = "Inventory"
            Range("AB" & cat).Offset(0, -1).Value = "Material not available causing backorder"
    End Select

Next cat

Upvotes: 7

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

I think you need to express equality in each condition. In other words, instead of this:

(Selection.Value = "hold to console" Or
 "Hold to console" Or
 "Allocated 14/12 and ship next day") Then

you need to use this:

(Selection.Value = "hold to console" Or
 Selection.Value = "Hold to console" Or
 Selection.Value = "Allocated 14/12 and ship next day") Then

Upvotes: 3

Related Questions