Opposite of LIKE statement

Question

I am having some issues getting a If NOT Like code to work.

Below is the part of the coding that has it. It seems like the coding is bringing over the second record that has Rail in the field being looked at, but none of the records with rail before that one or after that one.

In reality, it should be bringing over records that don't contain rail anywhere in that field but it doesn't do that either. Any feedback is appreciated.

Set rngData = Intersect(wsData.UsedRange, wsData.Range("A:K"))

    j = 1
    rngData.Rows(1).Copy Destination:=wsGalreq.Cells(j, 1)

    For i = 2 To rngData.Rows.Count
        If Not rngData.Cells(i, 11).Value = "SUPPLIER A" And rngData.Cells(i, 6).Value Like "*RAIL*" Then
            j = j + 1
            rngData.Rows(i).Copy Destination:=wsGalreq.Cells(j, 1)
        End If
    Next

Here's the [Operator Precedence table](https://msdn.microsoft.com/en-us/library/office/gg278455.aspx) in VBA for future reference — barrowc, Mar 09 '16 at 02:37

score 10 · Accepted Answer · answered Mar 08 '16 at 21:35

10

You should use brackets or logic to explicitly state what you are after.

If Not rngData.Cells(i, 11).Value = "SUPPLIER A" And rngData.Cells(i, 6).Value Like "*RAIL*" Then

is the same as

If Not A And B

when it appears you want

If Not A And Not B

answered Mar 08 '16 at 21:35

Alex McMillan

17,096
12
55
88

or it could be `IF Not (A And B)` – Scott Craner Mar 08 '16 at 21:38
Perfect @Alex McMillan, I was able to use: IF A And Not B as I actually wanted it to select Supplier A records but not ones that included "Rail" in the description. Thanks! – Danny Mar 08 '16 at 21:41

score 2 · Answer 2 · edited May 23 '17 at 12:31

As VBA doesn't short circuit it is better to write logic as below with the more likely False condition first (so the redundant second test isn't run).

So

If Not A Then If Not B

Or in your case;

If Not rngData.Cells(i, 11).Value = "SUPPLIER A" Then
    If Not rngData.Cells(i, 6).Value Like "*RAIL*" Then
            j = j + 1
            rngData.Rows(i).Copy Destination:=wsGalreq.Cells(j, 1)
    End If
 End If

Opposite of LIKE statement

2 Answers2