Need interpretation

  • Thread starter Thread starter JMay
  • Start date Start date
J

JMay

Sub test()
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)" <<See Note
End Sub

First please verbalize the "=SUM(R[-7]C:R[-1]C)" syntax, I can't get it..

<<Note : this line created separately using Macro recorder with Cell A8
active at the time. It appears to be an absolute address (with the 7 in
it).
How can I change the "=SUM(R[-7]C:R[-1]C)" portion
on code to always run from one cell up to the top row of my column?

Thanks in Advance,,
 
it means to sum in the current column -7 cells up from this cell down to -1
cell from this cell (this cell being the cell with the formula). To sum
from the first cell in the column of the active cell to one cell above the
active cell, you would use
ActiveCell.FormulaR1C1 = "=SUM(R1C:R[-1]C)"

R1C is absolute R1 this column
R[-1]C is the cell above the cell with the formula (this is relative to
row)
 
Thanks Tom; I've printed your comments in order to study.
JMay

Tom Ogilvy said:
it means to sum in the current column -7 cells up from this cell down to -1
cell from this cell (this cell being the cell with the formula). To sum
from the first cell in the column of the active cell to one cell above the
active cell, you would use
ActiveCell.FormulaR1C1 = "=SUM(R1C:R[-1]C)"

R1C is absolute R1 this column
R[-1]C is the cell above the cell with the formula (this is relative to
row)

--
Regards,
Tom Ogilvy


Sub test()
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)" <<See Note
End Sub

First please verbalize the "=SUM(R[-7]C:R[-1]C)" syntax, I can't get it..

<<Note : this line created separately using Macro recorder with Cell A8
active at the time. It appears to be an absolute address (with the 7 in
it).
How can I change the "=SUM(R[-7]C:R[-1]C)" portion
on code to always run from one cell up to the top row of my column?

Thanks in Advance,,
Click to expand...
Click to expand...
 
Back
Top