Worksheet Function countif

[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?

 

[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:""")

 

[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

 

[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.