Variable inside function range

[dna1711]

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,

 

[JLatham]

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]

Thank you, what you suggested does indeed do what I want. Obviously I'm new
to VBA and don't quite understand the syntax. Why does:
endaddr = Range(Active_addr).Offset(boxsize, 0).Address
work with boxsize (being a variable = 9) and what I attempted to do in the
Average line not work? And where in Help is its description located?

Regards,

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])"

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,

 

[JLatham]

I think the answer to both questions is that while in VB, the variable
BoxSize is evaluated to see what its value is before anything is done with
it. In your question, the .Offset(boxsize, 0) portion is evaluated to
determine what the value is, and it is found to be 9 and that's the actual
value used to get the result.

Same for the concatenated formula that I provided: BoxSize is evaluated and
its current value (9) is used in the formula, not the literal words BoxSize.
However, the way you wrote it initially, it is made a literal part of the
formula. But back in the Excel worksheet, Excel doesn't know anything about
a variable or name called BoxSize - it goes looking for it, doesn't find it
and throws up in your lap. If you had had a cell named BoxSize in the
workbook, it would have picked up the value from that cell and evaluated it
and used its value in the formula.

Hope this explanation helps clear things up a little for you.

Thank you, what you suggested does indeed do what I want. Obviously I'm new
to VBA and don't quite understand the syntax. Why does:
endaddr = Range(Active_addr).Offset(boxsize, 0).Address
work with boxsize (being a variable = 9) and what I attempted to do in the
Average line not work? And where in Help is its description located?

Regards,

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])"

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,