How to use Excel formulas in VBA
 

I would like to know how to use the standard Excel
formulas in VBA. For example, one function that I am
trying to use is the average function. When I record a
macro following the steps to perform this function it
gives me the following:

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-31]C:R[-1]C)"

The catch is that the column I need to average will vary
in size. I can store the size of the column by indexing.
My question is how do I place this variable in the formula
above to calculate the average? Is it possible or is
there another format that must be used? Any help would be
greatly appreciated.

Thanks,
Jeff

How to use Excel formulas in VBA
 

Sub tester3()
r1 = 3
r2 = 100
Cells(2, 5).FormulaR1C1 = "=Average(R" & r1 & "C:R" & r2 & "C)"
End Sub

Using absolute references

--
Regards,
Tom Ogilvy


Jeff Armstrong wrote in message
...
I would like to know how to use the standard Excel
formulas in VBA. For example, one function that I am
trying to use is the average function. When I record a
macro following the steps to perform this function it
gives me the following:

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-31]C:R[-1]C)"

The catch is that the column I need to average will vary
in size. I can store the size of the column by indexing.
My question is how do I place this variable in the formula
above to calculate the average? Is it possible or is
there another format that must be used? Any help would be
greatly appreciated.

Thanks,
Jeff

How to use Excel formulas in VBA
 

try like this

Sub avera()
Ran = Range(Cells(1, 1), Cells(14, 1))
T = Application.WorksheetFunction.Average(Ran)
Cells(15, 1) = T
End Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/