Sum using Variable

[Fatz]

Hi-

The following code will sum the 14 rows above the row I am in. I want
to replace the -14 below with a variable (i). How do I code the below
to work with a variable?

ActiveCell.FormulaR1C1 = "=SUM(R[-14]C:R[-1]C)"

Thank You!

Chris

 

[Joel]

ActiveCell.FormulaR1C1 = "=SUM(R[" & i & "]C:R[-1]C)"

 

[Tom Hutchins]

Try

ActiveCell.FormulaR1C1 = "=SUM(R[" & i & "]C:R[-1]C)"

Remember, i must be a negative whole number in this example.

Hope this helps,

Hutch

 

[Norman Jones]

Hi Chris,

To replace the negative constant (-14) with a varible, try:

Dim i As Long

i = -14

ActiveCell.FormulaR1C1 = "=SUM(R[" & i & "]C:R[-1]C)"


By the way, I believet that your formula
sums the 13 cells below the actice cell,
rather than the 14 cells above it.

 

[Fatz]

Hi Chris,

To replace the negative constant (-14) with a varible, try:

    Dim i As Long

    i = -14

    ActiveCell.FormulaR1C1 = "=SUM(R[" & i & "]C:R[-1]C)"

By the way, I believet that your formula
sums the 13 cells below the actice cell,
rather than the 14 cells above it.

---
Regards.
Norman

Hi-

The following code will sum the 14 rows above the row I am in.  I want
to replace the -14 below with a variable (i).  How do I code the below
to work with a variable?

ActiveCell.FormulaR1C1 = "=SUM(R[-14]C:R[-1]C)"

Thank You!

Chris- Hide quoted text -

- Show quoted text -

Thanks to everyone! It works great.....

Chris