| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Run-Time error '91': Object variable of With block variable not set | Excel Programming | |||
| why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
| Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
| Cells.Find error Object variable or With block variable not set | Excel Programming | |||
| Pivot Table - Object variable or with block variable not set? | Excel Programming | |||
Linear Mode
