ActiveCell.FormulaR1C1 = "=SUM(R[-(Range("j1").Value)]C:R[-1]C)"

I am trying to put a formula in the active cell to sum a list of numbers starting from 1 row up, to the number of rows up that is equal to the value in the cell j1. In other word if the value in the cell j1 was 9, I need to sum from 1 row up to 9 rows up.

Thank you in advance for any help.

Activecell.value="=Sum(R" & Range("J1").value & ":R9)"

Try

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


--
Darren

"hal" wrote in message
...
ActiveCell.FormulaR1C1 = "=SUM(R[-(Range("j1").Value)]C:R[-1]C)"

I am trying to put a formula in the active cell to sum a list of numbers
starting from 1 row up, to the number of rows up that is equal to the value
in the cell j1. In other word if the value in the cell j1 was 9, I need to
sum from 1 row up to 9 rows up.

Thank you in advance for any help.

Thanks for your help, works great!

If it were not for the need to double the quotes, the formula that would end up in the cell
would be

=SUM(R[-(Range("j1").Value)]C:R[-1]C)

which of course makes no sense on the worksheet -- it doesn't know anything about Range and
Value and ranges in quotes.

For easier reading and debugging, I usually construct these kinds of formulas with placeholders,
then, use Replace to fill in the variable pieces:

Dim sFormula As String

sFormula = "=SUM(R[-###]C:R[-1]C)"
sFormula = Replace(sFormula, "###", Range("J1").Value)
ActiveCell.FormulaR1C1 = sFormula

If there are multiple variable to be replaced, be sure to use unique placeholders for each, i.e.
###, $$$, @@@, ^^^, XX1, XX2, XX3, XY1, etc.

On Fri, 7 Nov 2003 17:36:08 -0800, hal wrote:

ActiveCell.FormulaR1C1 = "=SUM(R[-(Range("j1").Value)]C:R[-1]C)"

I am trying to put a formula in the active cell to sum a list of numbers starting from 1 row
up, to the number of rows up that is equal to the value in the cell j1. In other word if the
value in the cell j1 was 9, I need to sum from 1 row up to 9 rows up.

Thank you in advance for any help.

I'm not the one who asked the question, but this technique is going to be
very handy for me on my current spreadsheet, so Thanks! :)

--
Darren

"Myrna Larson" wrote in message
...
If it were not for the need to double the quotes, the formula that would
end up in the cell
would be

=SUM(R[-(Range("j1").Value)]C:R[-1]C)

which of course makes no sense on the worksheet -- it doesn't know
anything about Range and
Value and ranges in quotes.

For easier reading and debugging, I usually construct these kinds of
formulas with placeholders,
then, use Replace to fill in the variable pieces:

Dim sFormula As String

sFormula = "=SUM(R[-###]C:R[-1]C)"
sFormula = Replace(sFormula, "###", Range("J1").Value)
ActiveCell.FormulaR1C1 = sFormula

If there are multiple variable to be replaced, be sure to use unique
placeholders for each, i.e.
###, $$$, @@@, ^^^, XX1, XX2, XX3, XY1, etc.

On Fri, 7 Nov 2003 17:36:08 -0800, hal
wrote:

ActiveCell.FormulaR1C1 = "=SUM(R[-(Range("j1").Value)]C:R[-1]C)"

I am trying to put a formula in the active cell to sum a list of numbers
starting from 1 row
up, to the number of rows up that is equal to the value in the cell j1. In
other word if the
value in the cell j1 was 9, I need to sum from 1 row up to 9 rows up.

Thank you in advance for any help.