Formulas in VBA
 

Tom,

Thanks for the help - however, I don't always have a list which is fillled
with numbers - It's basically a list that grows or shrinks (based on the
number of years of data I have for a certain product.) and includes blank
cells at times. What I do know is how many rows of data I'd need at any
particular point. There may be times when I had to average a list of 10
rows, or at times an average of 6 rows.
I need to display the formula as well - is there any way to do this?

Thanks,

Haas



"Tom Ogilvy" wrote in message
...
Assuming your data starts in Cell A1 and has no blank cells down to the
last
cell.

Range("A1").End(xldown).offset(1,0).FormulaR1C1 = _
"=Average(R1C1:R[-1]C1)"


or

Cells(rows.count,1).End(xlup)(2).FormulaR1C1 = _
"=Average(R1C1:R[-1]C1)"

Regards,
Tom Ogilvy


"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