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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[vba] VBA Count cells in column containing specified value

Examples related to vba

Examples related to excel