Try
Dim Aa As Integer
Dim Bb As Integer
'data range is from A14 to A17 and the formula is put in A18
Aa = 14 'start of data
Bb = 17 'end of data
Range("A18").Value = Application.WorksheetFunction.Average(Range("Sheet 1!A"
& Aa & ":A" & Bb))

"Haas" wrote in message
om...
HELP!!!

I need to show a formula in a cell which would calculate the average
of a list of numbers. Recording a macro yielded the following results:

Range("A25").Select
ActiveCell.FormulaR1C1 = "=average(R[-18]C:R[-3]C)"


Here's the problem: the list of numbers is always either growing or
shrinking, so the formula would not always be in Cell A25, and we
wouldn't always be offsetting by 18 rows, etc. This would be simple to
do if I din't need to show the formula itself, but I have to.

I tried the following:

a = -18
b = -3
Range("A25").Select
ActiveCell.FormulaR1C1 = "=average(R[a]C:R[b]C)"


(This was done for example purposes - I will be using offsets to
achieve selecting a growing or shrinking range...)

I received an error message - I guess you can't use an R1C1 convention
with variables. Is there another way to do this? I need to show the
formula in the cell due to data manipulation purposes; also, this
would be part of a program which would have other calculations and a
requirement has been that formulas be shown.

Any help on this matter will be greatly appreciated!

Thanks,


Haas