Sunday, February 16, 2025
No menu items!
HomeData Analytics and VisualizationVBA: How to Use Like Operator

VBA: How to Use Like Operator

This tutorial explains how to use Like Operator in VBA.

Like Operator is used to compare a string against a pattern.

Syntax of Like Operator
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.

1. Highlight the Names Starting with letter ‘A’

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.

VBA : Highlihght Names Using Like operator
2. Emails With Exactly Three Characters Before “@”

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.

VBA: Like Operator in VBA
3. Phone Numbers Containing ‘444’

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.

VBA: Wildcards with Like Operator in VBA
4. Product IDs ending with ‘3’

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.

VBA : How to use like opearator in VBA

Read MoreListenData

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments