Adding a sum next to each line that says sub total

Question

So I have a set of data that is updated monthly. On the spreadsheet, the data is grouped in blocks of rows with a line for a subtotal after each block, and a line at the bottom that totals all of the sub-totals.

I want to add a code so that the data is summed in the row where it says "sub-total", by adding all of the lines above until the previous line that says "sub-total"

E.g.

Cleaning 8000
Sweeping 2000
Litter 5000
SUB TOTAL 15000 <--- sum of the three above
Chipseal 6000
Asphalt 3000
Milling 5000
SUB TOTAL 14000 <--- sum of chipseal, asphalt and milling
TOTAL 29000 <--- sum of the sub totals

HELP!

We really can't help without knowing which part of your code / formulae is giving you problems. (Unless, perhaps, your "HELP!" is meant to be interpreted as "Will someone please do this for me so that I can just sit back and relax.") — YowE3K, Feb 10 '17 at 01:40
You are partially correct at deducing "Help". I haven't written the correct coding yet. I set a long which finds the row containing "sub total" but then I don't know how to code the range that will pick up the values between the two lines that say "sub total" and then moves on to the next row with "sub total" in it. — gourdblessamerica, Feb 10 '17 at 02:04
IMO it would be best to set a variable acting as a subtotal and another one as a total. Loop through each row and either (1) if it isn't a "SUB TOTAL" or "TOTAL" add the value from the row to both variables or (2) if it is a "SUB TOTAL" write out to the row the subtotal variable and then set your subtotal variable to zero or (3) if it is a "TOTAL" write out to the row the total variable. — YowE3K, Feb 10 '17 at 02:07

Amorpheuses · Answer 1 · 2017-02-13T02:12:47.710

Try the code as posted. I assume your item descriptions are in column 'A' and your costs are in column 'B'. What it does is to place an indirect sum into 'B' column adjacent to each 'SUB TOTAL' string it finds (typed that way ignoring case). I also assume the values start at row 1 which you'll probably not want it to do. I accumulate a sum string and place it in the row immediately after the last subtotal row I find. Each subtotal cell will be given a cyan color background and a top border. Presumably you'll be able to continue on from this point and modify it to suit your needs.

Function findSubTotalRows(lastRow As Integer) As Collection
  Dim regEx As New RegExp
  Dim subTotCols As Collection

  regEx.Global = True
  regEx.IgnoreCase = True
  regEx.Pattern = "^SUB TOTAL$"

  Dim row As Integer
  Dim val As String
  Set subTotCols = New Collection
  For row = 1 To lastRow:
    val = Trim(Cells(row, 1).Value)
    Set mat = regEx.Execute(val)
    If mat.Count = 1 Then
      subTotCols.Add row
    End If
  Next

  Set findSubTotalRows = subTotCols
End Function


Sub sum_up_subtotals()
 Dim lastRow As Integer
 Dim cols As Collection

 ' Find last row in column and all sub total rows
 lastRow = Range("A1000").End(xlUp).row
 Set cols = findSubTotalRows(lastRow)

 Dim prevRow As Integer: prevRow = 0
 Dim numRng As Integer
 Dim totStr As String: totStr = "=SUM("
 For row = 1 To cols.Count:
   thisRow = cols(row)
   numRng = thisRow - prevRow - 1
   With Cells(thisRow, 2)
     .Formula = "=SUM(INDIRECT(ADDRESS(ROW()-" & CStr(numRng) & ",COLUMN())&"":""&ADDRESS(ROW()-1,COLUMN())))"
     .Interior.Color = vbCyan
     .NumberFormat = "$#,##0.00"
     .Borders(xlEdgeTop).LineStyle = xlContinuous
   End With
   prevRow = thisRow
   totStr = totStr & "B" & thisRow & ","
 Next

 totStr = Mid(totStr, 1, Len(totStr) - 1) & ")"
 Cells(thisRow + 1, 2).value = totStr

End Sub

The nice thing about doing it this way is that you can insert additional rows into each of the subtotal segments or add new subtotal segments, run the macro and it should show the proper new sums.

It works for me but I just tried it with the data you provided. Note that you have to have regular expressions enabled for this to work.

I'm getting an error on the last line "cells(thisrow+1,2)..." Even after I modify it to match my sheet. It's running error 1004, but unfortunately I have no idea where to edit because I haven't worked with regexp or collections before...I'll persevere though, thanks for this. — gourdblessamerica, Feb 13 '17 at 01:48
I've added .value to that line and updated the code above - see if that works. — Amorpheuses, Feb 13 '17 at 02:13
Stopped the error by adding a space in the field that defines totStr = " =SUM(". However, now it just returns that text in one cell and nothing else. Oh well, I'll persevere :) — gourdblessamerica, Feb 16 '17 at 02:24
Make sure you have regular expressions enabled (see this [post](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) - ie the one with 454 beside it - follow the instructions listed there). Then try again. Also let me know what version of excel you're running (you can do that by following the instructions [here](https://support.office.com/en-us/article/What-version-of-Office-am-I-using-932788b8-a3ce-44bf-bb09-e334518b8b19) — Amorpheuses, Feb 16 '17 at 03:23

score 0 · Answer 2 · answered Feb 21 '17 at 20:17

Don't worry guys, I found a simpler code which loops through every line looking for "TOTAL" and adds a sum formula in that row. Then the starting row becomes the line below the sub total row and the process starts again.

In this case ws is defined as a worksheet, firstRow and x are integers, lastrow is a long

firstRow = 4
For x = 4 To lastRow
    If ws.Range("C" & x) Like "*TOTAL*" Then
        ws.Range("E" & x).Formula = "=sum(E" & firstRow & ":E" & x - 1 & ")"
        firstRow = x + 1
    End If
Next x

Adding a sum next to each line that says sub total

2 Answers2