You can use the following basic syntax to count the number of cells in a range that contain a specific text using VBA:

Sub CountCellsWithText()
    Range("D2") = WorksheetFunction.CountIf(Range("A2:A13"), "*avs*")
End Sub

This particular example counts the number of cells in the range A2:A13 that contain “avs” and then assigns the result to cell D2.

Note: The asterisks ( * ) are used as wildcard characters in the Countif function.

If you would instead like to display the count of cells in a message box, you can use the following syntax:

Sub CountCellsWithText()

    Dim cellCount As Integer
    
    'Calculate number of cells that contain 'avs'
    cellCount = WorksheetFunction.CountIf(Range("A2:A13"), "*avs*")
    
    'Display the result
    MsgBox "Cells that contain avs: " & cellCountEnd Sub

The following examples shows how to use each of these methods in practice with the following dataset in Excel that contains information about various basketball players:

Example 1: Count Cells with Specific Text Using VBA and Display Results in Cell

Suppose we would like to count the number of cells that contain “avs” in the team name and output the results in a specific cell.

We can create the following macro to do so:

Sub CountCellsWithText()
    Range("D2") = WorksheetFunction.CountIf(Range("A2:A13"), "*avs*")
End Sub

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

Notice that cell D2 contains a value of 5.

This tells us that the there are 5 cells in the range A2:A13 that contain “avs” in the team name.

Example 2: Count Cells with Specific Text Using VBA and Display Results in Message Box

We can create the following macro to do so:

Sub CountCellsWithText()

    Dim cellCount As Integer
    
    'Calculate number of cells that contain 'avs'
    cellCount = WorksheetFunction.CountIf(Range("A2:A13"), "*avs*")
    
    'Display the result
    MsgBox "Cells that contain avs: " & cellCountEnd Sub

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

The message box tells us that there are 5 cells that contain “avs” in the team name.