title: VBA Excel - Inserting Subtotal Formulas into Specific Rows
body: <p>My issue is kind of a blend of what's happening <a href="https://stackoverflow.com/questions/25739774/vba-excel-summing-a-range-of-cells-based-on-result-in-a-different-range-of-cel">here</a> and <a href="https://stackoverflow.com/questions/22610818/how-to-add-groups-based-on-conditions-in-excel">here</a>, but is very much neither. I've got a weird dataset that is structured more like a hierarchy than a pure series of data points. Something like this:</p>
  
  <pre><strong>Item                           Budget      Sales</strong>
  GROUP - Cats                      0         120
  FY 13 Persian                     0           0
  FY 13 Maine Coon                 12           0
  FY 14 Maine Coon                 50           0
  FY 12 Tabby                       1           0
  FY 13 Tabby                       1           0
  FY 14 Tabby                       2           0
  FY 14 Alley                      12           0
  GROUP - Dogs                      0         201
  FY 14 Collie                     20           0
  FY 14 Lab                        31           0
  FY 13 Golden Retriever           12           0
  FY 12 Golden Retriever            0           0
  GROUP - Gold Fish                 0          50
  FY 14 Goldfish                  100           0
  FY 13 Clown Fish                 20           0
  Tanks Fees                      150           0
  </pre>
  
  <p>I need a macro that can neatly identify the GROUP lines and then sum the group underneath it -- without capture the next group. In other words, I need the cat budget line to sum only the cat budgets.</p>
  
  <p>So the macro would need to identify the line with the "GROUP*", then search down until it finds the next "GROUP*", and sum the space in between Cats and Dogs -- preferably as a function -- on the "GROUP - Cats" line.</p>
  
  <p>I know this has to be possible, but I'm worried it's too complicated for my basic abilities in VBA.</p>
  
  <p><strong>EDIT:</strong> The end product would be a formula in the Budget column that is simply =SUM(B3:B9). Then B10 (the Dogs GROUP) would have a formula such as =SUM(B11:B14). For Gold Fish: =SUM(B16:18).</p>
  
  <p>But since each my dataset is always changing (for instance, this week there may be 20 lines in the Cats section instead of 18 the previous week), I need a macro that can find the space between the GROUP lines.</p>
  
  <p><strong>EDIT 2:</strong> The VBA I'm using currently does something similar to what I'm looking for -- it essentially groups and collapses my sections based on the numbers that appear in the Sales column:</p>
  
  <pre><code>Dim rStart As Range, r As Range
  Dim lLastRow As Long, sColumn As String
  Dim rColumn As Range
  '### The Kittens everywhere! thing is just to make sure the last group has an end point
  Range("C1").End(xlDown).Offset(1).Value = "Kittens everywhere!"
      sColumn = "C"
  
  With ActiveSheet.Outline
          .AutomaticStyles = False
          .SummaryRow = xlAbove
          .SummaryColumn = xlRight
  
  lLastRow = Cells(Rows.Count, sColumn).End(xlUp).Row
  With ActiveSheet
      Set rColumn = .Range(.Cells(1, sColumn), Cells(lLastRow, sColumn))
      With rColumn
          Set r = .Cells(1, 1)
          Do Until r.Row &gt; lLastRow
              If rStart Is Nothing Then
                  If r.Value = "0" Then
                      Set rStart = r
                  End If
              Else
                  If r.Value &lt;&gt; "0" Then
                      Range(rStart, r.Offset(-1, 0)).Rows.Group
                      Set rStart = Nothing
                  End If
              End If
              Set r = r.Offset(1, 0)
          Loop
      End With
  End With
  ActiveSheet.Outline.ShowLevels RowLevels:=1
  End With
  
  Range("C:C").Find("Kittens everywhere!").ClearContents
  </code></pre>
  
  <p>There's more to the macro -- because it's also doing some things like highlighting the GROUP rows -- but I'm sure if I can jam this SUM function stuff into that section or not.</p>