This tutorial explains how to use Like Operator
in VBA.
Like Operator
is used to compare a string against a pattern.
Variable Like pattern
Variable
is the string we want to compare. pattern
is the search pattern we are comparing against which can include wildcards.
You can download the following dataset to practice.
Let us consider a few examples to understand the practical applications of Like Operator
.
The following code can be used to highlight all names that begin with ‘A’ in column A of the dataset.
Sub HighlightNamewithA() Dim ws As Worksheet Dim searchPattern As String Dim lastRow As Long Dim Cell As Range Set ws = ThisWorkbook.Sheets("Sheet1") lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row searchPattern = "A*" For Each Cell In ws.Range("A1:A" & lastRow) If Cell.Value Like searchPattern Then Cell.Interior.Color = vbRed End If Next Cell End Sub
Press Run or F5 to run the above code.
The following code can be used to highlight emails with exactly three characters before the ‘@’ in column B of the dataset.
Sub email() Dim ws As Worksheet Dim searchPattern As String Dim lastRow As Long Dim Cell As Range Set ws = ThisWorkbook.Sheets("Sheet1") lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row searchPattern = "???@*" For Each Cell In ws.Range("B1:B" & lastRow) If Cell.Value Like searchPattern Then Cell.Interior.Color = vbRed End If Next Cell End Sub
Press Run or F5 to run the above code.
In this case, let’s highlight all the phone numbers in column C that contain ‘444’ in them.
Sub phonenumber() Dim ws As Worksheet Dim searchPattern As String Dim lastRow As Long Dim Cell As Range Set ws = ThisWorkbook.Sheets("Sheet1") lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row searchPattern = "*444*" For Each Cell In ws.Range("C1:C" & lastRow) If Cell.Value Like searchPattern Then Cell.Interior.Color = vbRed End If Next Cell End Sub
Press Run or F5 to run the above code.
We can use the following code to highlight all the product IDs (in column D) that end with ‘3’ :
Sub productid() Dim ws As Worksheet Dim searchPattern As String Dim lastRow As Long Dim Cell As Range Set ws = ThisWorkbook.Sheets("Sheet1") lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row searchPattern = "*3" For Each Cell In ws.Range("D1:D" & lastRow) If Cell.Value Like searchPattern Then Cell.Interior.Color = vbRed End If Next Cell End Sub
Press Run or F5 to run the above code.
Read MoreListenData