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: " & cellCount
End 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**

Suppose we would instead like to count the number of cells that contain “avs” and output the results in a 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: " & cellCount
End 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.

**Additional Resources**

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

VBA: A Simple Formula for “If Cell Contains”

VBA: How to Count Occurrences of Character in String

VBA: How to Check if String Contains Another String