# Thread: range summation (xl97/xl2000 )

1. ## range summation (xl97/xl2000 )

Hi everyone,
I have a range of 40 cells (one row) that I want to do a summation on -- cellA = cellA, cellB = cellA + cellB, cellC = cellA + cellB, etc. I need this in VBA and I can get the WorksheetFunction.Sum to return me a sum,but it's one number (the total of all cells) where what I want is a range returned which now contains the cumulative totals of each cell.

What I do now is loop thru the cells but that's ugly and I'm sure there's some slick formula way to do it (but it's got to be run in VBA not from Excel).

TIA, Deb <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>

2. ## Re: range summation (xl97/xl2000 )

I'm sure you need more than this, but the basic formula you require is

ActiveCell.FormulaR1C1 = "=RC[-1]+R[-1]C[-1]"
ActiveCell.Offset(1, 0).Range("A1").Select

I'm just learning VBA myself & this was a simple recorded macro using relative referencing. I'm sure someone else will have the definitive answer.

3. ## Re: range summation (xl97/xl2000 )

Hi Deb,

Something like this might do what you want:

Sub InsertCumulativeSum()

' Number of rows below your values
' that the calculations will appear
Const OFFSET_ROWS As Long = 1

' Object variable for the range
' containing the values...
Dim rngVals As Range

' Simple way to test... you will
' obviously set the range according
' to the existing parameters of your
' program rather than by using
' the Selection object...
Set rngVals = Selection

' Enter a simple set of sums across
' the range...
With rngVals.Cells(1)
rngVals.Offset(OFFSET_ROWS, 0). _
FormulaR1C1 = _
"=SUM(R" & .Row & "C" & _
.Columns & ":R[-" & _
OFFSET_ROWS & "]C)"
End With

End Sub

HTH

Rob

4. ## Re: range summation (xl97/xl2000 )

Hmmm, this is more complex than I figured it be. It seems like you still need to iterate through each cell manually to get the summed range which isn't much different than my FOR LOOP.

Thanks for the code Rob and TIR, I'll play with it today and see how it works. I guess I now realize that the Excel formulas are meant to return one answer/value not an entire range.

Much appreciated!
Deb <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•