Table of Contents
The NOT LIKE operator is a Boolean operator that can be used in Visual Basic for Applications (VBA) to compare two strings and determine if they are not equal. It is typically used when searching for strings that are not exactly the same, such as when searching a database for records with similar values. This operator can be used in combination with the LIKE operator to create more complex search criteria. Examples of the NOT LIKE operator used in VBA code are given to illustrate how it works.
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.
The following tutorials explain how to perform other common tasks using VBA: