VBA: How to Use “NOT LIKE” (With Examples)

You can use the Not statement along with the Like statement in VBA to determine if strings do not contain a specific pattern.

For example, you can use the following syntax to check if each string in the cell range A2:A10 does not contain the substring “hot” and output the results in the range B2:B10:

Sub CheckNotLike()

    Dim i As Integer
    
    For i = 2 To 10
        If Not Range("A" & i) Like "*hot*" Then
            Range("B" & i) = "Does Not Contain hot"
        Else
            Range("B" & i) = "Contains hot"
        End If
    Next i
    
End Sub

The following example shows how to use this syntax in practice.

Example: How to Use NOT LIKE in VBA

Suppose we have the following list of foods in column A in Excel:

We can create the following macro to check if each string in column A does not contain the substring “hot” and output the results in column B:

Sub CheckNotLike()

    Dim i As Integer
    
    For i = 2 To 10
        If Not Range("A" & i) Like "*hot*" Then
            Range("B" & i) = "Does Not Contain hot"
        Else
            Range("B" & i) = "Contains hot"
        End If
    Next i
    
End Sub

When we run this macro, we receive the following output:

Column B shows whether or not each corresponding cell in column A contains the substring “hot” or not.

Note: We used asterisks ( * ) around the substring to indicate that any character can come before or after the string “hot” in the cell.

Additional Resources

The following tutorials explain how to perform other common tasks using VBA:

VBA: How to Count Occurrences of Character in String
VBA: How to Check if String Contains Another String
VBA: How to Count Cells with Specific Text