How to use Excel formulas in VBA

[Jeff Armstrong]

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

 

[Tom Ogilvy]

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

Using absolute references

 

[speidlbacsi]

try like this

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