I think you probably need to do something like this to create the formula:

ActiveCell.FormulaR1C1 = _
"=AVERAGE(RC[-1]:R[" & BoxSize & "]C[-1])"

That'll put whatever value is contained in BoxSize into the formula rather
than putting the word "BoxSize" into the formula and having Excel fail
because it has no real idea of what the hell BoxSize is other than a word, as
it would in this formula:

"=AVERAGE(RC[-1]:R[BoxSize]C[-1])"


"dna1711" wrote:

Hi all,

I have an averaging macro working with the following line in it:
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-1]:R[9]C[-1])"
the line averages 10 rows of data in the same column. What I want to do is
change the "9" to a variable that I use elsewhere in the macro (boxsize) so
that the number of averaged cells changes automatically. But when I
substitute the variable (boxsize, which = 9) into the line above, I get an
error. Could someone please tell me why it doesn't work and what can I do to
fix it.

Thanks,