VBA sum previous cells

[bookworm121]

How would you go through each column in worksheet called "Main" and create summed up columns in worksheet called "Work".
When summing, each cell is it's own value + all previous values in a column

What i mean by summed up columns is this:

If in Main: column A has these values:

1
2
5
6

Then in Work I want these values in column A:

1
3
8
14

And this needs to be done for all columns in worksheet "Main"

 

[JoeMo]

How would you go through each column in worksheet called "Main" and create summed up columns in worksheet called "Work".
When summing, each cell is it's own value + all previous values in a column

What i mean by summed up columns is this:

If in Main: column A has these values:

1
2
5
6

Then in Work I want these values in column A:

1
3
8
14

And this needs to be done for all columns in worksheet "Main"

Try this:

Sub CumFromMainToWork()
Dim lRM As Long, c As Range, S As Double
lRM = Sheets("Main").Range("A" & Rows.Count).End(xlUp).Row
For Each c In Sheets("Main").Range("A1:A" & lRM)
    If IsNumeric(c.Value) Then
        S = S + c.Value
        If c.Row = 1 Then
            Sheets("Work").Range("A1") = S
        Else
            Sheets("Work").Range(c.Address) = S
        End If
    End If
Next c
End Sub

 

[JLGWhiz]

How would you go through each column in worksheet called "Main" and create summed up columns in worksheet called "Work".
When summing, each cell is it's own value + all previous values in a column

What i mean by summed up columns is this:

If in Main: column A has these values:

1
2
5
6

Then in Work I want these values in column A:

1
3
8
14

And this needs to be done for all columns in worksheet "Main"

And another way.

Sub sumUp()
Dim sh As Worksheet, c As Range, rng As Range, lr As Long, sRng As Range
Set sh = Sheets(1)
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & lr)
    For Each c In rng
        Set sRng = sh.Range("A2:A" & c.Row)
        c.Offset(0, 1) = Application.Sum(sRng)
    Next
End Sub

 

[Peter_SSs]

I think both the suggestions so far have missed the information that there are multiple columns in 'Main' to be processed and/or that the results should go in a different sheet called 'Work'.

In any case, here's a non-looping suggestion to try in a copy of your workbook.
This may need some modification if the columns in 'Main' use different numbers of rows and you don't like the output from this code.
I've assumed 'Work' exists and any existing data in it can be cleared. If this is not the case post back with more details & I'll modify.

Sub Cumulative_Totals()
  With Sheets("Work").Range(Sheets("Main").UsedRange.Address)
    .Parent.UsedRange.ClearContents
    .FormulaR1C1 = "=SUM('Main'!R1C:RC)"
    .Value = .Value
  End With
End Sub

 

[handclips]

Try this:

Sub CumFromMainToWork()
Dim lRM As Long, c As Range, S As Double
lRM = Sheets("Main").Range("A" & Rows.Count).End(xlUp).Row
For Each c In Sheets("Main").Range("A1:A" & lRM)
    If IsNumeric(c.Value) Then
        S = S + c.Value
        If c.Row = 1 Then
            Sheets("Work").Range("A1") = S
        Else
            Sheets("Work").Range(c.Address) = S
        End If
    End If
Next c
End Sub

Came across this old thread.

Would anyone know what changes are needed if i want to multiply instead of add?

I tried changing to S = S * c.Value but it gave all 0s.

Trying to achieve these results:

1 1
2 2
5 10
6 60
8 480

Thanks for helping!

 

[Peter_SSs]

Would anyone know what changes are needed if i want to multiply instead of add?

Assuming the original values in column A starting in row 1 and results in column B, try the code below.

BTW, I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sub Progressive_Product()
  With Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=PRODUCT(R" & .Row & "C[-1]:RC[-1])"
    .Value = .Value
  End With
End Sub

 

[handclips]

Came across this old thread.

Would anyone know what changes are needed if i want to multiply instead of add?

I tried changing to S = S * c.Value but it gave all 0s.

Trying to achieve these results:

1 1
2 2
5 10
6 60
8 480

Thanks for helping!

Assuming the original values in column A starting in row 1 and results in column B, try the code below.

BTW, I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sub Progressive_Product()
  With Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=PRODUCT(R" & .Row & "C[-1]:RC[-1])"
    .Value = .Value
  End With
End Sub

Thanks for your suggestion, really appreciate it! Just wondering if it's possible to do the same with for and next?

Your version works perfectly fine, I'm trying to use this as an example to learn more

 

[handclips]

Thanks for your suggestion, really appreciate it! Just wondering if it's possible to do the same with for and next?

Your version works perfectly fine, I'm trying to use this as an example to learn more

Sorry, wanted to say using for/next and no Excel formulas.

 

[Peter_SSs]

wondering if it's possible to do the same with for and next?

Here is one way

Sub Progressive_Product_v2()
  Dim a As Variant
  Dim i As Long
  
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 2 To UBound(a)
      a(i, 1) = a(i, 1) * a(i - 1, 1)
    Next i
    .Offset(, 1).Value = a
  End With
End Sub

 

[handclips]

Here is one way

Sub Progressive_Product_v2()
  Dim a As Variant
  Dim i As Long
 
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 2 To UBound(a)
      a(i, 1) = a(i, 1) * a(i - 1, 1)
    Next i
    .Offset(, 1).Value = a
  End With
End Sub

Thanks so much!