CODEWITHSUNDEEP
vbaexcel
Kim
Kim

Reputation: 163

Error 1004: Unable to Get CountIf Property

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.

Upvotes: 1

Views: 1167

Answers (2)

Graham Monkman
Graham Monkman

Reputation: 432

Probably not directly applicable here, but I was encountering this error when doing a countif on a listobject range on which i'd filtered a column. I removed the filter and the error dissappeared.

Upvotes: 0

SeanC
SeanC

Reputation: 15923

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.

Upvotes: 1

Related Questions