Log In
Register
Home
New Member
June 13, 2022
Offline
Dear All,
I am facing error - Runtime Error 1004 - Unable to get Averageif property of Worksheet Function class in my Excel VBA code. THe code is as shown below.
Sub CaptureSeasonality()
Dim wb As Workbook, ws As Worksheet, LastRow As Long
Set wb = ThisWorkbook
Set ws = wb.Sheets("Calculations")
Worksheets("Calculations").Activate
With Sheets2
LastRow = Cells.CurrentRegion.Rows.Rows.Count
Range("=Calculations!H2").Value = "Seasonality"
Dim rng1 As Range, rng2 As Range
Set rng1 = ws.Range(Cells(7, 3), Cells(17, 3))
Set rng2 = ws.Range(Cells(7, 7), Cells(17, 7))
Dim x As Long
For x = 1 To LastRow
Range("=Calculations!H" & x + 2).Value = Round(WorksheetFunction.AverageIf(rng1, (ws.Cells((x + 2), 3).Value), rng2), 3)
Next
End With
End Sub
Trusted Members
Moderators
November 1, 2018
Offline
Your workbook code is password protected, which doesn't really allow people to help you easily. Anyway, the most likely cause is that nothing matches the condition in your AverageIf function, so the result is an error, not a number. Up to you what you want to do in that situation but you can handle it with an error handler or use Application.AverageIf which will return an error value rather than raising a runtime error, and you can test that with IsError before trying to use it.
Also, it's polite to mention cross-posting:
1 Guest(s)
