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 · 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

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

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

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

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

[vba] VBA Count cells in column containing specified value

Examples related to vba

Examples related to excel