To create the countif formula using VBA excel.
In this VBA Excel automation, we had data in cell A2:B6. Here wanted to find the count of IDs in column A, which have zone as “North” in column B. We have applied the formula in cell D2 as seen in the image below. We have used the formula property of range object.
In the code below, we had declared 2 variables (1) CellRange (2) CountCriteria. We can also get the values of these 2 variables by user input to make the code more dynamic. But for demonstration purpose we shall initialize them in the code itself.
Sub CountifFormula() Dim CellRange As String Dim CountCriteria As String CellRange = "A2:B6" CountCriteria = "North" Range("D2").Formula = "=countif( " & CellRange & " ," & """" & CountCriteria & """" & ")" End Sub
As we can see in the image that string “North” has appeared 2 times in column B, one time for SID 10 and one time for SID 11 . So, the result is 2 which is showcased in cell D2.
- Formula can be seen in the formula bar.
- We have used 2 variables to make the code more dynamic.
Post you may like