CODEWITHSUNDEEP
vbaexcel
user2828701
user2828701

Reputation: 305

VBA Average Function Error - 1004

I am getting an error 'Unable to get average property of Worksheetfunction class' when attempting to calculate the average of 2 ranges.

My table can be found below.

When I calculate the average of the % column, I have no problems. It provides me with the correct output, however, I have problems when I try and calculate the average for the $ column.

Col |   %    | $ 
1   | 2.33%  | $2.33
2   | 3.64%  | $3.64 
3   | 10.83% | $10.83
4   | 6.07%  | $6.07
5   |   -    | - 
6   | 12.99% | $12.99
7   | 18.99% | $18.99


  Dim myRange As Range
  Dim myAverage As Variant

 'The user selects the range
 Set myRange = Application.InputBox( _
 prompt:="Please select the range", Title:="My Range", Type:=8)

 'This splits the range into two areas because the user typically does not select the row with the "-" in it.  
 'myRange would typically look something like (B1:B4,B6:B7) OR (C1:C4,C6:C7)
 Area1 = myRange.Areas(1)
 Area2 = myRange.Areas(2)

 myAverage = Application.WorksheetFunction.Average(Area1, Area2)

The error I receive is 'Unable to get average property of Worksheetfunction class' and it happens with the myAverage calculation.

Any ideas as to why it calculates the % column without problem, but doesn't calculate the $ column?

Thanks in advance!

Upvotes: 0

Views: 1884

Answers (1)

David Zemens
David Zemens

Reputation: 53663

Declare as the proper type (Range object):

Dim Area1 As Range
Dim Area2 As Range

Use the Set keyword to assign to Object variables.

Set Area1 = myRange.Areas(1)
Set Area2 = myRange.Areas(2)

Then, you should be able to use the Application.Average or Application.WorksheetFunction.Average to get the mean.

myAverage Application.Average(Area1, Area2)

Otherwise, you're passing a variant array to the function, which is not supported, hence it raises the error. The Average function requires either a contiguous range of multiple cells, or itemized list of individual cells or values. You can't pass it multiple ranges of multiple cells each.

Or, omit the Area1/Area2 steps entirely and do simply:

myAverage = Application.Average(myRange.Areas(1), myRange.Areas(2))

Upvotes: 3

Related Questions