Whats wrong with this VBA statement -ActiveCell.FormulaR1C1...?
 

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.

Whats wrong with this VBA statement -ActiveCell.FormulaR1C1...?
 

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

Whats wrong with this VBA statement -ActiveCell.FormulaR1C1...?
 

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.

Whats wrong with this VBA statement -ActiveCell.FormulaR1C1...?
 

Thanks for your help, works great!

Whats wrong with this VBA statement -ActiveCell.FormulaR1C1...?
 

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.

Whats wrong with this VBA statement -ActiveCell.FormulaR1C1...?
 

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.