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

[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:

When a user adds a detail line, I need the header to reflect the sum of the detail lines below it until the next header line. I have 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

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!

 

[Joe4]

Someone a lot smarter than me may have a better way of doing it, but here is one way that should work:

Sub MyInsertSums()

    Dim lr As Long, nr As Long, r As Long
    
    Application.ScreenUpdating = False
    
    Sheets("GL Import Data Batches").Activate

'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
    nr = 1
    
'   Loop through column A
    Do
'       Find ending row for sum
        If Cells(nr + 2, "B") = "" Then
            r = nr + 1
        Else
            r = Cells(nr + 1, "B").End(xlDown).Row
        End If
'       Insert sum formula
        Cells(nr, "B").Formula = "=SUM(B" & nr + 1 & ":B" & r & ")"
'       Move down to next row
        nr = Cells(nr, "A").End(xlDown).Row
    Loop Until nr = Rows.Count
    
    Application.ScreenUpdating = True
    
End Sub

 

[Darranimo]

Someone a lot smarter than me may have a better way of doing it, but here is one way that should work:

Sub MyInsertSums()

    Dim lr As Long, nr As Long, r As Long
   
    Application.ScreenUpdating = False
   
    Sheets("GL Import Data Batches").Activate

'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
   
    nr = 1
   
'   Loop through column A
    Do
'       Find ending row for sum
        If Cells(nr + 2, "B") = "" Then
            r = nr + 1
        Else
            r = Cells(nr + 1, "B").End(xlDown).Row
        End If
'       Insert sum formula
        Cells(nr, "B").Formula = "=SUM(B" & nr + 1 & ":B" & r & ")"
'       Move down to next row
        nr = Cells(nr, "A").End(xlDown).Row
    Loop Until nr = Rows.Count
   
    Application.ScreenUpdating = True
   
End Sub

So this almost works. However, imagine the blue cells in the image I shared in my original post not having any equations then running this macro. So basically only the detail lines and then filling in the equation for the header. Right now this macro sums everything below each header line including the next header and the next detail. So using the same picture I shared, when I run your code, cell B1 ends up equaling 100 instead of 40.

 

[Joe4]

Hmmm...
It does not do that for me. I tried it on the data example you presented, and it returned the EXACT values you show in the blue cells.
That leads me to believe that you real data may be a little different than what you show.

Are the empty cells in columns A and B REALLY empty, or do they contain spaces, formulas, or other things in them?

Can you provide a small sample of your REAL data, as it actually exists? MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

If you post the sample using this tool, it will allow me to copy exactly what you posted, so hopefully we are testing the same thing.

 

[Peter_SSs]

Assuming that the numbers in column B are not the result of formulas, try this

Sub Add_Totals()
  Dim rA As Range
  
  For Each rA In Columns("B").SpecialCells(xlConstants, xlNumbers).Areas
    rA.Cells(0).Formula = "=SUM(" & rA.Address & ")"
  Next rA
End Sub

 

[Darranimo]

Assuming that the numbers in column B are not the result of formulas, try this

Sub Add_Totals()
  Dim rA As Range
 
  For Each rA In Columns("B").SpecialCells(xlConstants, xlNumbers).Areas
    rA.Cells(0).Formula = "=SUM(" & rA.Address & ")"
  Next rA
End Sub

You're the man Peter! This is perfection! Thank you!

 

[Darranimo]

Assuming that the numbers in column B are not the result of formulas, try this

Sub Add_Totals()
  Dim rA As Range
 
  For Each rA In Columns("B").SpecialCells(xlConstants, xlNumbers).Areas
    rA.Cells(0).Formula = "=SUM(" & rA.Address & ")"
  Next rA
End Sub

One thing I just noticed. I do have a label above column A and column B... is there a way to modify this to exclude the labels? Currently the code replaces the label in column B with a sum equation too. I tried using a range instead of "Columns("B")" but that doesn't seem to fix it.

 

[Darranimo]

You're the man Peter! This is perfection! Thank you!

I may have jumped the gun...

This code works great if there is no sum in column B. However, after it places the equation, if a new detail line is added and the code is ran again it does not update the equation to include the newly added amount.

 

[Peter_SSs]

I may have jumped the gun...

This code works great if there is no sum in column B. However, after it places the equation, if a new detail line is added and the code is ran again it does not update the equation to include the newly added amount.

Perhaps I am not understanding just what you have done.

My initial sheet

Darranimo.xlsm
	A	B
1	ABC
2		10
3		10
4		10
5		10
6	DEF
7		5
8		5
9		5
10		5
11		5
12		5
13
Sheet1

After running the code

Darranimo.xlsm
	A	B
1	ABC	40
2		10
3		10
4		10
5		10
6	DEF	30
7		5
8		5
9		5
10		5
11		5
12		5
13
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUM($B$2:$B$5)
B6	B6	=SUM($B$7:$B$12)

Inserted a new row 6 and added a value in B6

Darranimo.xlsm
	A	B
1	ABC	40
2		10
3		10
4		10
5		10
6		100
7	DEF	30
8		5
9		5
10		5
11		5
12		5
13		5
14
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUM($B$2:$B$5)
B7	B7	=SUM($B$8:$B$13)

Now run the code again

Darranimo.xlsm
	A	B
1	ABC	140
2		10
3		10
4		10
5		10
6		100
7	DEF	30
8		5
9		5
10		5
11		5
12		5
13		5
14
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUM($B$2:$B$6)
B7	B7	=SUM($B$8:$B$13)

As I understand it, that is what you would want?
Just remember my assumption though. Is it correct?

Assuming that the numbers in column B are not the result of formulas