Sum using Variable
 

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

Sum using Variable
 

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


"Fatz" wrote:

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

Sum using Variable
 

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

"Fatz" wrote:

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

Sum using Variable
 

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

"Fatz" wrote in message
...
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

Sum using Variable
 

On May 5, 2:01*pm, "Norman Jones"
wrote:
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

"Fatz" wrote in message

...



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