CODEWITHSUNDEEP
excelvba
M Renlow
M Renlow

Reputation: 36

Unable to get Averageifs property of WorksheetFunction class

I created a macro which summarizes a series of Excel forms into outputs, organized by sheets, rows and columns. The program creates a sheet, populates a table, and repeats.

The only thing I have left to do is to populate the cells with a formula. It's a very simple AverageIfs formula and I am getting the "Unable to get Averageifs property of WorksheetFunction class" error. I tried a bunch of things that I read, but to no avail.

So far I

Here is the relevant excerpt of my code. I am omitting a large portion, but I can assure all variables are defined and the loop works properly if I replace the AverageIfs function with a hard value (e.g. "1").

'Start Loop
VBid = 0
ZEval = 0

Do While VBid <> NumBidder
    ActiveBid = ListInputSheet.Range("H" & 7 + VBid).Value
    Set AnalysisSheet = AnalysisBook.Sheets.Add
    AnalysisSheet.Name = ActiveBid

    XRow = 4
    AnalysisSheet.Range("C" & XRow).Value = ActiveBid
    ZEval = 0

    'Loop all evaluators down rows
    Do While ZEval <> NumEval
        ActiveEval = ListInputSheet.Range("E" & 7 + ZEval).Value
        AnalysisSheet.Range("D" & XRow).Value = ActiveEval
        AnalysisSheet.Range("D" & XRow).Select
        WSub = 0

        'Loop all Category Averages across row in columns
        Do While WSub <> NumCategories
            ActiveCell.Offset(0, 1).Select

            Form = Application.WorksheetFunction.AverageIfs(SummarySheet.Range("F1:F10000"), SummarySheet.Range("A1:A10000"), ActiveEval, SummarySheet.Range("B1:B10000"), ActiveBid, SummarySheet.Range("D1:D10000"), WSub + 1)
            ActiveCell.Value = Form

            WSub = WSub + 1
        Loop

        XRow = XRow + 1
        ZEval = ZEval + 1

    Loop
    'End evaluator loop
    VBid = VBid + 1
Loop
'End Bidder loop

Upvotes: 1

Views: 2142

Answers (2)

Sudhakar
Sudhakar

Reputation: 11

I have encountered the same error and couldn'd find a reason for a while, but later figured out that it is a issue with the data. It seems for the average function we need more than one row of data to work, so when I added additional rows of data it started working fine without any issue.

Upvotes: 1

Excel Hero
Excel Hero

Reputation: 14764

Here's an alternative if you cannot get it going. Just swap this line of code for you AverageIfs line:

    Form = SummarySheet.Evaluate("AverageIfs(F1:F10000,A1:A10000," & ActiveEval & ",B1:B10000," & ActiveBid & ",D1:D10000," & WSub + 1 & ")")

Upvotes: 0

Related Questions