PDA

View Full Version : Syntax on Application.Average



Saladsamurai
11-25-2009, 07:58 AM
This is a stupid question, but I am having a lot of trouble finding help on this one:

I am trying to use the "Average" worksheet function. Due to the nature of my code, it is more convenient for me to refer to ranges using the 'Cells()' method.

This works:
RowAverage = Application.Average(Cells(i + 3, 2), Cells(i + 3, 29))

But this only returns the average of the 2 cells. I want the average of all cells in the RANGE Cells(i + 3, 2) To Cells(i + 3, 29)/

I tried

This
Application.Average(Cells(i + 3, 2) : Cells(i + 3, 29))

This
Application.Average("Cells(i + 3, 2)" : "Cells(i + 3, 29)")

Even this
Application.Average(Cells(i + 3, 2) To Cells(i + 3, 29))

but all have syntax errors....Any thoughts?

Saladsamurai
11-25-2009, 08:06 AM
This is stupid. I just tried using the regular old Average syntax in VBA

Application.WorkSheetFunction.Average(A1:A20)

and it gave me a syntax error. Apparently in VBA, Average() will only take arguments like this:

Application.WorkSheetFunction.Average(arg1, arg2, arg3)

Bob Phillips
11-25-2009, 08:10 AM
Application.Average(Range(Cells(i + 3, 2), Cells(i + 3, 29)) )