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

[Darranimo]

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:

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:

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!

 

[Max1616]

Give this a try:

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

 

[Osvaldo Palmeiro]

Maybe:

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

 

[Fluff]

Duplicate to: Summing until next data point in a different column. Should be easy for someone on here. Lol.

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.