Forum

Runtime Error 1004 ...
 
Notifications
Clear all

Runtime Error 1004 - Unable to get Averageif property of Worksheet Function class

2 Posts
2 Users
0 Reactions
337 Views
(@sangamk_81)
Posts: 1
New Member
Topic starter
 

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 SubExcel-VBA-Error.png

 
Posted : 14/06/2022 7:25 am
(@debaser)
Posts: 838
Member Moderator
 

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: 

https://www.mrexcel.com/board/threads/excel-run-time-error-1004-unable-to-get-averageif-property-of-worksheetfunction-class.1207682/

 
Posted : 15/06/2022 3:37 am
Share: