title: Multiple (nested) Subtotal VBA Code with Inserting Sum Formula in the Subtotal
body: <p>I would like to create a nested subtotal macro, meaning to have one subtotal for column “countries” and one subtotal for column “cities.” I use the below code to get the subtotal for countries, but I don’t know how to expand the code to get subtotal for cities.</p>
  
  <p>One condition in my code is that the sum formula should exist in the subtotal row to make sure having the correct range.  </p>
  
  <p>I tried different ways to get the subtotal for cities, but the range of sum formula will be changed as the macro inserts a new to create for a new subtotal.</p>
  
  <p>My idea is to have a code in two steps:</p>
  
  <ol>
  <li>Get the subtotal for countries as hard values ---> having hard values help us avoid the distortion of formula as macro creates subtotal for cities</li>
  <li>Get the subtotal for cities as sum formulas 
  Here is the current code I used to create subtotal for countries as sum formula inside.</li>
  </ol>
  
  <p>The Code I use for creating a subtotal for column countries:</p>
  
  <pre><code>Dim iCol As Integer 'number of columns
  Dim i As Integer 'Macro starts from this row number
  Dim j As Integer 'Macro continues with this row number in the loop
  
  Worksheets("Example").Activate
  
  Application.ScreenUpdating = False
  i = 2 'starts from row 2
  j = i
  'Loops throught Col A Checking for match then when there is no match add Sum
  Do While Range("A" &amp; i) &lt;&gt; ""
      If Range("A" &amp; i) &lt;&gt; Range("A" &amp; (i + 1)) Then
          Rows(i + 1).Insert
          Range("A" &amp; (i + 1)) = "Subtotal " &amp; Range("A" &amp; i).Value
          For iCol = 3 To 4 'Columns to Sum
              Cells(i + 1, iCol).Formula = "=SUM(R" &amp; j &amp; "C:R" &amp; i &amp; "C)"
          Next iCol
          Range(Cells(i + 1, 1), Cells(i + 1, 4)).Font.Bold = True
          Range(Cells(i + 1, 1), Cells(i + 1, 4)).Interior.Color = RGB(221, 237, 245)
          i = i + 2
          j = i
      Else
          i = i + 1
      End If
  Loop
  Application.ScreenUpdating = True
  </code></pre>
  
  <p>Here is the screenshot of what I would like to have after running the code.</p>
  
  <p><a href="https://i.stack.imgur.com/Kngo3.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Kngo3.jpg" alt="Original Data Before Implemeting the code"></a></p>
  
  <p>This is my desired output</p>
  
  <p><a href="https://i.stack.imgur.com/Ou7kD.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Ou7kD.jpg" alt="This is my desired output"></a></p>
  
  <p>I appreciate your help.</p>