Worksheet Function countif

H

Hydra

Set Myrange = Worksheets("Sheet1").Range("a1, a" & nrows)
NumberOfSummaries = Application.WorksheetFunction.CountIf(Myrange,
"SUMMARY:")

this throws and error that says "Unable to get countif function of the
worksheet function class"

Any idea what I'm doing wrong?
 
J

Joel

I think you just needed more double quotes around Summary. the worksheet
function whant tho se double quotes and excel is removing these quotes

Set Myrange = Worksheets("Sheet1").Range("a1, a" & nrows)
NumberOfSummaries = Application.WorksheetFunction.CountIf(Myrange,
"""SUMMARY:""")
 
J

Jacob Skaria

Range is to be represented as Range("A1:A" & nrows). Try the below code


Set Myrange = Worksheets("Sheet1").Range("A1:A" & nrows)

'for an exact cell match
NumberOfSummaries = Application.WorksheetFunction.CountIf(Myrange, "SUMMARY:")

OR

'to count cells containing SUMMARY:
NumberOfSummaries = Application.WorksheetFunction.CountIf(Myrange,
"*SUMMARY:*")


If this post helps click Yes
 
H

Hydra

Ugh,
You are right, I need a colon instead of a comma.

Also thanks for the tip on a partial match.

Hydra



My work around is to wrte the formula into a blank cell and then read the
value of the cell back out to a variable, then empty the cell.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top