Worksheet Function countif
 

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?

Worksheet Function countif
 

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


"Hydra" wrote:

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?

Worksheet Function countif
 

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
---------------
Jacob Skaria


"Hydra" wrote:

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?

Worksheet Function countif
 

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.

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Hydra" wrote:

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?