Add every range of cells say 31 cells

[kelly mort]

Hi,
I am looking for a script that will add from say G3 to G33 then place the result in G34. From there, add from G35 to G65 then place result in G66. And continue till the last used row. Here let us track the last used row from column A.

Thanks in advance

 

[Rick Rothstein]

Hi,
I am looking for a script that will add from say G3 to G33 then place the result in G34. From there, add from G35 to G65 then place result in G66. And continue till the last used row. Here let us track the last used row from column A.

Does this do what you want...

[table="width: 500"]
[tr]
	[td]Sub Sum31Cells()
  Dim R As Long, LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For R = 34 To LastRow Step 32
    Cells(R, "G").Value = Application.Sum(Cells(R, "G").Offset(-31).Resize(31))
  Next
End Sub[/td]
[/tr]
[/table]

 

[alansidman]

Sub Add_30()
    Dim i As Long, lr As Long, j As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    j = 34
    For i = 3 To lr Step 31
        Range("G" & j).FormulaR1C1 = "=SUM(R[-31]C:R[-1]C)"
        j = j + 32
    Next i
End Sub

 

[kelly mort]

Does this do what you want...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Sum31Cells()
  Dim R As Long, LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For R = 34 To LastRow Step 32
    Cells(R, "G").Value = Application.Sum(Cells(R, "G").Offset(-31).Resize(31))
  Next
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Exactly! !!

Very cool.

Thanks a lot

 

[kelly mort]

Sub Add_30()
    Dim i As Long, lr As Long, j As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    j = 34
    For i = 3 To lr Step 31
        Range("G" & j).FormulaR1C1 = "=SUM(R[-31]C:R[-1]C)"
        j = j + 32
    Next i
End Sub

Oh okay I recorded this macro I forgot how to fix that loop.

Thanks for the fix!!!