CODEWITHSUNDEEP
vbaexcel
S. Rock
S. Rock

Reputation: 11

Run-time error '1004' for WorksheetFunction.AverageIf

My current issue involves using the AverageIf function to calculate average times for certain events (flavor Change, Size Change, and Label Changes). I wrote three separate sections of code to calculate each average time for each event. All three sections are identical (same criteria range, and average range) the only difference is the the criteria.

However, when I run the macro I get the following error:

Run-time error '1004' Unable to get the AvergeIf property of WorkksheetFunction class

This error only occurs at the Label Change Section of Code.

Some of my troubleshooting:

Here is a snippet of my code:

'Set Criteria Range for Averages
Set crng = Range(Worksheets("DataImport").Cells(StartRowT, "E"), Worksheets("DataImport").Cells(LastRowT, "E"))
'Set Average time ranfe for Averages
Set trng = Range(Worksheets("DataImport").Cells(StartRowT, "G"), Worksheets("DataImport").Cells(LastRowT, "G"))

'Flavor Change
If I > 0 Then
    FlavorA = Application.WorksheetFunction.AverageIf(crng, "Flavor Change", trng)
Else
    FlavorA = 0
End If
'Size Change
If M > 0 Then
    SizeChangeA = Application.WorksheetFunction.AverageIf(crng, "Size Change", trng)
Else
    SizeChangeA = 0
End If
'Label Change
If J > 0 Then
    LabelA = Application.WorksheetFunction.AverageIf(crng, "Label Change", trng)
Else
    LabelA = 0
End If

I am still trying to figure out the exact cause of the run-time error 1004 for the label change section of the code.

Upvotes: 1

Views: 1720

Answers (2)

Shai Rado
Shai Rado

Reputation: 33692

This error is caused since you don't have any values in your crng range with the value of "Label Change", so you are actually trying to divide by zero - #DIV/0.

You can add a criteria to test before calculating the Average of the range, use the code below (per each Average calculation)

' at least once cell in the range has the value of "Label Change"
If WorksheetFunction.CountIf(crng, "Label Change") > 0 Then
    LabelA = Application.WorksheetFunction.AverageIf(crng, "Label Change", trng)
Else
    ' pop a message-box, or whatever you want
    MsgBox "'Label Change' value is not found in range " & crng.Address
End If

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166835

If the criteria do not match (and you get #DIV/0) then this would trigger a run-time error in your VBA. Instead you can use the Application.AverageIf() version (i.e. drop the WorksheetFunction) which will not throw a run-time error, and then test the return value using IsError()

Dim m

'Label Change
If J > 0 Then
    m = Application.AverageIf(crng, "Label Change", trng)
    LabelA = IIf(IsError(m),"value when no match", m)
Else
    LabelA = 0
End If

Upvotes: 3

Related Questions