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

Hello Zach,

I am trying to count only Bus numbers between W285 to W300. However, below code is not working. It works for overall but specific Bus numbers.

Any assistance is appreciated.

Thanks

Sub CountUnique()

Dim BusNum As Range, List As Object, UniqueCount As Long

Set List = CreateObject(“Scripting.Dictionary”)

If BusNum Like “W###” Then

BusNum = Right(BusNum, 3)

If BusNum >= 285 And BusNum <= 300 Then

BusNum = "W" & BusNum

Else: BusNum = 99999

End If

End If

For Each BusNum In Range("O_O_S")

If Not List.Exists(BusNum.Value) Then List.Add BusNum.Value, Nothing

Next

UniqueCount = List.count

Range("_Notes1").Value = UniqueCount

End Sub