Variable cumulative R1C1(??) notation

[ewan7279]

Hi all,

I have some code that updates a table of information. This table is a
different number of rows in length each time. I need a cumulative total in
column H to add up column G in the table from the bottom up, so the bottom
total equals the value in the cell to the left, the next cell up equals the
previous total plus the cell to the left and so on. So far, I have:

Dim EndRng as Range
Dim formulaconst as String

------

Sheets(1).Range("G4").End(xlDown).Select 'selects the first value to be
totalled
EndRng = ActiveCell 'defines the range of the value
formulaconst = ActiveCell.Address 'to enter into formula(??)
EndRng.Offset(0, 1).Select 'first cell to enter formula
ActiveCell.Formula = "=" & formulaconst 'I can't get further than this!!

Please help.
Ewan.

 

[Jim Rech]

One way:

Range(Range("G4"), Range("G4").End(xlDown)).Offset(0, 1).FormulaR1C1 _
= "=SUM(R4C[-1]:RC[-1])"


--
Jim
| Hi all,
|
| I have some code that updates a table of information. This table is a
| different number of rows in length each time. I need a cumulative total
in
| column H to add up column G in the table from the bottom up, so the bottom
| total equals the value in the cell to the left, the next cell up equals
the
| previous total plus the cell to the left and so on. So far, I have:
|
| Dim EndRng as Range
| Dim formulaconst as String
|
| ------
|
| Sheets(1).Range("G4").End(xlDown).Select 'selects the first value to be
| totalled
| EndRng = ActiveCell 'defines the range of the value
| formulaconst = ActiveCell.Address 'to enter into formula(??)
| EndRng.Offset(0, 1).Select 'first cell to enter formula
| ActiveCell.Formula = "=" & formulaconst 'I can't get further than this!!
|
| Please help.
| Ewan.
|

 

[ewan7279]

Hi Jim,

thanks for your reply, but this actually sums the cumulative total down from
G4, rather than up from the last value in column G, which is what I really
want this to do. Is this possible through a formula, or will I have to
resort the data etc (which I'd rather avoid as, logically, it only makes
sense to sum up from the bottom of this particular stack of data)?

 

[ewan7279]

....so the last row in the table is the constant rather than the 4th row?

Hi Jim,

thanks for your reply, but this actually sums the cumulative total down from
G4, rather than up from the last value in column G, which is what I really
want this to do. Is this possible through a formula, or will I have to
resort the data etc (which I'd rather avoid as, logically, it only makes
sense to sum up from the bottom of this particular stack of data)?

One way:

Range(Range("G4"), Range("G4").End(xlDown)).Offset(0, 1).FormulaR1C1 _
= "=SUM(R4C[-1]:RC[-1])"


--
Jim
| Hi all,
|
| I have some code that updates a table of information. This table is a
| different number of rows in length each time. I need a cumulative total
in
| column H to add up column G in the table from the bottom up, so the bottom
| total equals the value in the cell to the left, the next cell up equals
the
| previous total plus the cell to the left and so on. So far, I have:
|
| Dim EndRng as Range
| Dim formulaconst as String
|
| ------
|
| Sheets(1).Range("G4").End(xlDown).Select 'selects the first value to be
| totalled
| EndRng = ActiveCell 'defines the range of the value
| formulaconst = ActiveCell.Address 'to enter into formula(??)
| EndRng.Offset(0, 1).Select 'first cell to enter formula
| ActiveCell.Formula = "=" & formulaconst 'I can't get further than this!!
|
| Please help.
| Ewan.
|

 

[ewan7279]

Hi Jim,

This is the solution I used (thanks to a bit of code from another post from
Ron De Bruin too):

With ActiveSheet
LastRow = .Cells(.Rows.Count, "g").End(xlUp).Row
End With
Range("G4").End(xlDown).Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R" & LastRow & "C[-1])"
ActiveCell.Copy
Range(ActiveCell, "H4").Select
Selection.PasteSpecial Paste:=xlFormulas

Thanks for your help.

 

[Jim Rech]

Range("G4").End(xlDown).Offset(0, 1).Select

Sorry, but a solution that uses Select gets an automatic 2 point deduction
in this Olympic year.<g>

--
Jim
| Hi Jim,
|
| This is the solution I used (thanks to a bit of code from another post
from
| Ron De Bruin too):
|
| With ActiveSheet
| LastRow = .Cells(.Rows.Count, "g").End(xlUp).Row
| End With
| Range("G4").End(xlDown).Offset(0, 1).Select
| ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R" & LastRow & "C[-1])"
| ActiveCell.Copy
| Range(ActiveCell, "H4").Select
| Selection.PasteSpecial Paste:=xlFormulas
|
| Thanks for your help.
|
| "Jim Rech" wrote:
|
| > One way:
| >
| > Range(Range("G4"), Range("G4").End(xlDown)).Offset(0, 1).FormulaR1C1
_
| > = "=SUM(R4C[-1]:RC[-1])"
| >
| >
| > --
| > Jim
| > | > | Hi all,
| > |
| > | I have some code that updates a table of information. This table is a
| > | different number of rows in length each time. I need a cumulative
total
| > in
| > | column H to add up column G in the table from the bottom up, so the
bottom
| > | total equals the value in the cell to the left, the next cell up
equals
| > the
| > | previous total plus the cell to the left and so on. So far, I have:
| > |
| > | Dim EndRng as Range
| > | Dim formulaconst as String
| > |
| > | ------
| > |
| > | Sheets(1).Range("G4").End(xlDown).Select 'selects the first value to
be
| > | totalled
| > | EndRng = ActiveCell 'defines the range of the value
| > | formulaconst = ActiveCell.Address 'to enter into formula(??)
| > | EndRng.Offset(0, 1).Select 'first cell to enter formula
| > | ActiveCell.Formula = "=" & formulaconst 'I can't get further than
this!!
| > |
| > | Please help.
| > | Ewan.
| > |
| >
| >
| >