How to use Excel formulas in VBA

  • Thread starter Thread starter Jeff Armstrong
  • Start date Start date
J

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
 
Sub tester3()
r1 = 3
r2 = 100
Cells(2, 5).FormulaR1C1 = "=Average(R" & r1 & "C:R" & r2 & "C)"
End Sub

Using absolute references
 
try like this

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