Using Loop to sum based on content in adjacent column then placing that sum.

Status
Not open for further replies.

Darranimo

New Member
Joined
Jan 19, 2022
Messages
38
Office Version
  1. 365
Platform
  1. Windows
I have a program that assembles data one line at a time by user input via a userform. Some of the lines are what I call "Header" lines and need to show the sum of what I call "Detail" lines below them. See the following image as a simplified example:
Capture.JPG

After a user adds all the detail lines, I need the header to reflect the sum of the detail lines below it until the next header line. So the picture above reflects the end result but prior to running the code the blue cells will be blank. Additionally the number of detail lines can be as little as one with a max of let's say 100. I had the following vba in solution in mind:

VBA Code:
Set rng = Worksheets("GL Import Data Batches").Range("A1:A100")
For Each cell In rng
If cell.Value <> "" Then cell.Offset(0, 2).Formula = "THIS IS WHERE I NEED HELP"
Next cell

^^This code works perfectly to place a formula in the correct spots but that is all it does. lol.

Basically, since only the header line will ever have data in column A I am thinking I can use that to loop through and add some kind of formula to sum only the detail corresponding to the header. Maybe it is not a formula but just more code that can accomplish this. Either way, I would really appreciate some help if you are able to. Thanks!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Max1616

Active Member
Joined
Nov 25, 2015
Messages
496
Office Version
  1. 2016
Platform
  1. Windows
Give this a try:
VBA Code:
Sub Header_Sums()
Dim i As Long
Dim LRow As Long
Dim Clm As Integer
Dim StartRow As Long
Dim PrevRow As Long
Dim ws As Worksheet

Set ws = Sheets("GL Import Data Batches")


LRow = ws.Cells(Rows.Count, 2).End(xlUp).Row
PrevRow = LRow + 1
StartRow = 30000
For i = LRow To 1 Step -1
    If ws.Cells(i, 1) <> "" Then
        StartRow = ws.Cells(StartRow, 1).End(xlUp).Row
        ws.Cells(StartRow, 2) = "=Sum(B" & StartRow + 1 & ":B" & PrevRow - 1 & ")"
        
        PrevRow = StartRow
    End If
Next i


End Sub
 
Solution

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
715
Office Version
  1. 365
Platform
  1. Windows
Maybe:

VBA Code:
Sub SumBelowValues()
 Dim r As Range
  For Each r In Range("B1:B" & Cells(Rows.Count, 2).End(3).Row).SpecialCells(4)
   r.Value = IIf(r.Offset(2).Value = "", r.Offset(1).Value, Evaluate("SUM(" & r.Offset(1).Address & ":" & r.Offset(1).End(4).Address & ")"))
  Next r
End Sub
 
Last edited:
Status
Not open for further replies.

Forum statistics

Threads
1,175,663
Messages
5,898,756
Members
434,729
Latest member
Tej92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top