What to do when AVERAGEIFS criteria does not match any data in a macro

[beedistinct]

I'm using the following in a macro and it seems that none of the criteria match, so while running the macro it gives me a run-Time error '1004'. "Unable to get the AverageIfs property of the WorksheetFunction class". How to handle this? I need to treat the average as a zero when the criteria doesn't match.

Average = WorksheetFunction.AverageIfs(AverageRange, Range, ">12/31/2009", Range, "<1/1/2011", StateRange, "<>Buy", StateRange, "<>Created", UrgentRange, "Urgent", ConditionRange, "<>")

 

[VoG]

Possibly like this

Dim Average As Variant
Average = Application.AverageIfs(AverageRange, Range, ">12/31/2009", Range, "<1/1/2011", StateRange, "<>Buy", StateRange, "<>Created", UrgentRange, "Urgent", ConditionRange, "<>")
If IsError(Average) Then
    Average = 0
End If

 

[Sektor]

I wonder what is Range as fourth argument? If you use named ranges, you'd write this way: Range("MyNamedRange").

 

[beedistinct]

Thank you, it worked!

The Range is actually defined as a variable with type Range. I changed its name to cull out our organization specific data.

Thanks for the tip there!

 

[beedistinct]

Possibly like this

Dim Average As Variant
Average = Application.AverageIfs(AverageRange, Range, ">12/31/2009", Range, "<1/1/2011", StateRange, "<>Buy", StateRange, "<>Created", UrgentRange, "Urgent", ConditionRange, "<>")
If IsError(Average) Then
    Average = 0
End If

Thanks so much, it worked the way you suggested!