Excel Run TIme Error 1004 - Unable to get Averageif Property of Worksheetfunction class

[sangamk_81]

Dear All,

I am facing the error - Excel Run TIme Error 1004 - Unable to get Averageif Property of Worksheetfunction class in below VBA code.

Request your help.

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

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: https://www.myonlinetraininghub.com...verageif-property-of-worksheet-function-class
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.