VBA Count cells in column containing specified value

Question

I need to write a macro that searches a specified column and counts all the cells that contain a specified string, such as "19/12/11" or "Green" then associate this number with a variable,

Does anyone have any ideas?

Answer 1

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.

Answer 2

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.