VBA IF Statement Combined with AND + OR

[rsmeyerson]

Hello,

I'm having trouble getting the 'AND' portion to work in the example code below. My issue is that FORMULA1 is being inserted into Column S when there is a Column J 'OR' match but not a Column E "NEW" match. Any help is greatly appreciated.

With Sheets("Retail")  
   
    Last = .Cells(.Rows.Count, "A").End(xlUp).Row
    For i = Last To 2 Step -1

        If (.Cells(i, "E").Value) = "NEW" And (.Cells(i, "J").Value) Like "*xxx*" Or (.Cells(i, "J").Value) Like "*yyy*" Or (.Cells(i, "J").Value) Like "*zzz*" Then
            .Cells(i, "S").Value = "FORMULA1"
        Else
            .Cells(i, "S").Value = "FORMULA2"
        End If
   
     Next i
End With

 

[John_w]

How about:

        If .Cells(i, "E").Value = "NEW" And (.Cells(i, "J").Value Like "*xxx*" Or .Cells(i, "J").Value Like "*yyy*" Or .Cells(i, "J").Value Like "*zzz*") Then

Be aware that by default the Like operator is case-sensitive, so Like "*xxx*" matches xxx, but not XXX. If you have Option Compare Text at the top of the module then Like is case-insensitive.

 

[Trebor76]

You could also add another IF statement to separate the condition like this:

Option Explicit
Sub Macro1()

    Dim Last As Long, i As Long
    
    Application.ScreenUpdating = False

    With Sheets("Retail")
        Last = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = Last To 2 Step -1
            If .Cells(i, "E").Value = "NEW" Then
                If .Cells(i, "J").Value Like "*xxx*" Or .Cells(i, "J").Value Like "*yyy*" Or .Cells(i, "J").Value Like "*zzz*" Then
                    .Cells(i, "S").Value = "FORMULA1"
                Else
                    .Cells(i, "S").Value = "FORMULA2"
                End If
            End If
         Next i
    End With
    
    Application.ScreenUpdating = True

End Sub

 

[rsmeyerson]

How about:

        If .Cells(i, "E").Value = "NEW" And (.Cells(i, "J").Value Like "*xxx*" Or .Cells(i, "J").Value Like "*yyy*" Or .Cells(i, "J").Value Like "*zzz*") Then

Be aware that by default the Like operator is case-sensitive, so Like "*xxx*" matches xxx, but not XXX. If you have Option Compare Text at the top of the module then Like is case-insensitive.

Thank you very much for your help. The parentheses after and solved the problem. Thanks again!