Using the COUNTIF Function in VBA in Excel (Code)

If you want to use the COUNTIF function in a VBA code, you need to use the WorksheetFunction property, which allows you to access all the Excel functions within a VBA code.

This tutorial will teach us to write and use COUNTIF in a code. For this, we have a few values in column A, and we need to count the values above 5000.

Write a VBA Code to use COUNTIF

Once you open the Visual Basic Editor from the developer tab, open a new module and create a new macro.

1. First, type “Application” and then enter a dot (.) to the list of methods and properties.
2. Now, select “WorksheetFunction” from the list or type it using the keyboard.
3. Again, type a dot (.) to get the list of the functions, and from there, select the COUNTIF.
4. Next, enter the starting parentheses to start the function. In the function’s first argument, specify the range from where you count the values with COUNTIF.
5. From here, in the second argument, use a greater than operator using double quotation marks and enter 5000 to use as criteria.
6. Last, you need to specify a cell to get the count in a cell.

Sub COUNTIF_VBA()
Range("B1") = Application.WorksheetFunction.CountIf(Range("A2:A11"), ">" & 5000)
End Sub

The above example shows that when you run the code, it returns the count in cell B1.

Important Note

When you use an Excel worksheet function in a VBA code using WorksheetFunction, it won’t show you the name of the arguments. So, you need to know the argument name before you write a code for the function.

In the above example, you can see that the names of the arguments are not there, but if you use COUNTIF in a worksheet, it shows you the names of the arguments.

If you want to get the result of COUNTIF in a message box, you can use the code below:

Sub COUNTIF_VBA()
MsgBox Application.WorksheetFunction.CountIf(Range("A2:A11"), ">" & 5000)
End Sub