Solved: count a specific text value in a column

[ckelley1020]

Hi. I need to use VBA to count any occurrences of only the word range in a column.
(I thought I figured this one out using COUNTIF, but am getting an error) Here is my code below that does NOT work

Range("H" & rangespot).Formula = "=COUNTIF(G1:G300,"Range")

I also need to add a sheet to the workbook I am on and place specific information on that sheet

thanks for any help:hi:

[shrivallabha]

If you have typed it as it is (above that is) :bug: Then all you need to do is add " (double quote):
Range("H" & rangespot).Formula = "=COUNTIF(G1:G300,"Range")"

[ckelley1020]

Here is what I actually had (sorry) I tried it using two different values as well

Range("H" & rangespot).Formula = "=COUNTIF(G1:G" & totalranges & ","Key Figures")"

Range("H" & rangespot).Formula = "=COUNTIF(G1:G200,"Range")"

I f I put them directly into the cell it works fine, but not in a macro


I get an "expected End of Statement" compile error

[shrivallabha]

Then try:
Range("H1").Formula = "=COUNTIF(G1:G200,""Range"")"
or if this fails then you can use macro recorder to get it correctly.

[ckelley1020]

Thank you. It works now. Seems strange you need a double quote there when it works with a single quote above for a different formula using COUNTA.

Excel is interesting to say the least! Thanks again:friends: