Do you mean you want to use a formula in VBA? Something like:
Dim iVal As Integer
iVal = Application.WorksheetFunction.COUNTIF(Range("A1:A10"),"Green")
should work.
Not what you asked but may be useful nevertheless.
Of course you can do the same thing with matrix formulas. Just read the result of the cell that contains:
Cell A1="Text to search"
Cells A2:C20=Range to search for
=COUNT(SEARCH(A1;A2:C20;1))
Remember that entering matrix formulas needs CTRL+SHIFT+ENTER, not just ENTER. After, it should look like :
{=COUNT(SEARCH(A1;A2:C20;1))}
one way;
var = count("find me", Range("A1:A100"))
function count(find as string, lookin as range) As Long
dim cell As Range
for each cell in lookin
if (cell.Value = find) then count = count + 1 '//case sens
next
end function
This isn't exactly what you are looking for but here is how I've approached this problem in the past;
You can enter a formula like;
=COUNTIF(A1:A10,"Green")
...into a cell. This will count the Number of cells between A1 and A10 that contain the text "Green". You can then select this cell value in a VBA Macro and assign it to a variable as normal.
If you're looking to match non-blank values or empty cells and having difficulty with wildcard character, I found the solution below from here.
Dim n as Integer
n = Worksheets("Sheet1").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
Source: Stackoverflow.com