Error 1004: Unable to Get CountIf Property

0
votes

I'm trying to search a range of named cells to see if there are any cells that contain a number greater than zero. Here is the code I currently have:

Dim YTDclauses As Boolean
Dim ytdrng As Range

Set ytdrng = Worksheets("Sheet1").Range("z1AY:z1BB,z1BG:z1BJ")

'Employer 1
If Sheet1.[z1AG] = "No" And WorksheetFunction.CountIf(ytdrng, ">0") = 0 Then
    MsgBox "Works!"
Else
    MsgBox "Does Not Work"
End If

I'm getting an error back as "Run-time error '1004': Unable to get the CountIfs property of the WorksheetFunction class". By looking at other questions, I think it might be a syntax error with how I'm setting ytdrng, but I've tried many ways of naming it differently to no avail. Any help is appreciated, thank you!

Note: Sheet1 is named "Main Checklist" - I also tried using that in the setting of ytdrng, but got the same error.

1
vbaexcel
that means that the object WorksheetFunction does not have a CountIf method/property ... or it means that WorksheetFunction is not an object that VBA knows about .... i think that you want application.worksheetfunction.countif – jsotola
I added the "application.", but am getting the same error. – Kim
You cannot use COUNTIF on a disconnected range. – Scott Craner
Does the equivalent Excel formula work? If not, why do you expect the equivalent VBA code to? – Mathieu Guindon
With a multi-area range you could loop over the areas and sum the counts... – Tim Williams

1 Answers

1
votes

As @ScottCraner has stated, you cannot do a countif on a split range. You can modify the routine slightly to implement a countif by looping over each cell in the range:

Dim YTDclauses As Boolean
Dim ytdrng As Range
Dim SRCountif As Long
Dim cel As Object

Set ytdrng = Worksheets("Sheet1").Range("z1AY:z1BB,z1BG:z1BJ")

SRCountif = 0
For Each cel In ytdrng.Cells
    If cel.Value > 0 Then SRCountif = SRCountif + 1
Next

'Employer 1
If Sheet1.[z1AG] = "No" And SRCountif = 0 Then
    MsgBox "Works!"
Else
    MsgBox "Does Not Work"
End If

(The variable SRCountif is meant to mean SplitRangeCountif)
Note that as it is comparing the value to numeric 0, Exec takes any text as greater than 0, so you may want to adjust the test if there is a chance of any text in your range.