# Loop through spreadsheet and replace static subtotals with formula

#### pr3ach3r

##### New Member
Hopefully this make sense.

I have this table I would like to loop through and change the subtotals to actual formulas.

https://imgur.com/YC0m2LP

My Thought was to loop through the A column because the subtotals are placed the row after the name. I thought if I could calculate the range between the last name I could use that to create a sum(). Any ideas on calculating that range?

The spreadsheet is much larger than that image.
Code:
``````Sub Cleanup()

countblank  'Function to get lastrow
Dim x As Integer
Dim CRng
Application.ScreenUpdating = False
' Set numrows = number of rows of data.
NumRows = ctblank
' Select cell a1.
Range("A2").Select
' Establish "For" loop to loop "numrows" number of times.
For x = 2 To NumRows
If (Not IsEmpty(Cells(x, 1)) And Not ActiveCell.Row = 2) Then

MsgBox LRow & "-" & Cells(x, 2).Offset(1, 0).Address
LRow = ""

'ActiveCell.Offset(1, 3).Value = "=Sum(" & sRegion & ")"

End If

ActiveCell.Offset(1, 0).Select
Next
Application.ScreenUpdating = True
End Sub``````

Last edited:

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### pr3ach3r

##### New Member
I think I figured it out with one exception:

The last row of subtotals it does not do that row.

Code:
``````Sub CleanupMasReport()

countblank  'Function to get lastrow
Dim x As Integer
Dim CRng
Application.ScreenUpdating = False
' Set numrows = number of rows of data.
NumRows = ctblank
' Select cell a1.
Range("A2").Select
'Cleanup zeros in report
Range("A2").Select
Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

' Establish "For" loop to loop "numrows" number of times.
LRow = Cells(2, 3).Row
For x = 3 To NumRows
If (Not IsEmpty(Cells(x, 1)) And Not ActiveCell.Row = 2) Then

ERow = Cells(x, 3).Offset(-1, 0).Row

ActiveCell.Offset(2, 2).Formula = "=Sum(C" & LRow & ":" & "C" & ERow & ")"
ActiveCell.Offset(2, 3).Formula = "=Sum(D" & LRow & ":" & "D" & ERow & ")"
ActiveCell.Offset(2, 4).Formula = "=Sum(E" & LRow & ":" & "E" & ERow & ")"
ActiveCell.Offset(2, 5).Formula = "=Sum(F" & LRow & ":" & "F" & ERow & ")"
ActiveCell.Offset(2, 6).Formula = "=Sum(G" & LRow & ":" & "G" & ERow & ")"
ActiveCell.Offset(2, 7).Formula = "=Sum(H" & LRow & ":" & "H" & ERow & ")"
ActiveCell.Offset(2, 8).Formula = "=Sum(I" & LRow & ":" & "I" & ERow & ")"
ActiveCell.Offset(2, 9).Formula = "=Sum(J" & LRow & ":" & "J" & ERow & ")"
ActiveCell.Offset(2, 10).Formula = "=Sum(K" & LRow & ":" & "K" & ERow & ")"
ActiveCell.Offset(2, 11).Formula = "=Sum(L" & LRow & ":" & "L" & ERow & ")"
ActiveCell.Offset(2, 12).Formula = "=Sum(M" & LRow & ":" & "M" & ERow & ")"
ActiveCell.Offset(2, 13).Formula = "=Sum(N" & LRow & ":" & "N" & ERow & ")"
ActiveCell.Offset(2, 14).Formula = "=Sum(O" & LRow & ":" & "O" & ERow & ")"

LRow = Cells(x + 1, 2).Row + 1

End If

ActiveCell.Offset(1, 0).Select
Next
MsgBox LRow
Application.ScreenUpdating = True
End Sub``````

Hopefully this make sense.

I have this table I would like to loop through and change the subtotals to actual formulas.

https://imgur.com/YC0m2LP

My Thought was to loop through the A column because the subtotals are placed the row after the name. I thought if I could calculate the range between the last name I could use that to create a sum(). Any ideas on calculating that range?

The spreadsheet is much larger than that image.
Code:
``````Sub Cleanup()

countblank  'Function to get lastrow
Dim x As Integer
Dim CRng
Application.ScreenUpdating = False
' Set numrows = number of rows of data.
NumRows = ctblank
' Select cell a1.
Range("A2").Select
' Establish "For" loop to loop "numrows" number of times.
For x = 2 To NumRows
If (Not IsEmpty(Cells(x, 1)) And Not ActiveCell.Row = 2) Then

MsgBox LRow & "-" & Cells(x, 2).Offset(1, 0).Address
LRow = ""

'ActiveCell.Offset(1, 3).Value = "=Sum(" & sRegion & ")"

End If

ActiveCell.Offset(1, 0).Select
Next
Application.ScreenUpdating = True
End Sub``````

1,106,543
Messages
5,511,947
Members
408,869
Latest member
MM005

### This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...