Results 1 to 4 of 4
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    Rob Bruce
    Guest

    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. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •