title: Insert Dynamic Subtotal Formulas with VBA
body: <p>I wrote a code which insert some formulas into the worksheet depending on the length of code of the item.</p>
  <p>I want it more dynamic and remove all &quot;d&quot; &quot;e&quot; like references in the Range formulas. The dynamic formula shall be according to the Last Column of the Range &quot;Budget&quot;.</p>
  <p>Thank you for your support.</p>
  <pre><code>Sub Macro1() 
  Dim LR, LastColumn, TotalColumn As Long
  Dim Kirilim1, Kirilim2, Kirilim3, ColumnNumberFromTotal As Integer
  Dim FoundCell As Range
  Dim ColumnLettersFromTotal As String
  LastColumn = ActiveSheet.Range(&quot;Budget&quot;).Columns.Count
  Set FoundCell = Cells.Find(What:=&quot;TOTAL&quot;, After:=ActiveCell, LookIn:=xlValues, LookAt _
           :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                      False)
  ColumnLettersFromTotal = Split(FoundCell.Address, &quot;$&quot;)(1)
  ColumnNumberFromTotal = Split(FoundCell.Address, &quot;$&quot;)(2)
  
  LR = (Cells(Rows.Count, 1).End(xlUp).Row)
  Kirilim1 = LR
  Kirilim2 = LR
  Kirilim3 = LR
  
  For i = LR To 1 Step -1
  
  
  
  If Range(&quot;B&quot; &amp; i).Value = 8 Then
  
      Range(&quot;d&quot; &amp; i).Value = &quot;=SUM(RC[1]:RC[&quot; &amp; (TotalColumn - ColumnNumberFromTotal + 1) &amp; &quot;])&quot;
  
      Kirilim2 = Kirilim2 - 1
  End If
          
  If Range(&quot;B&quot; &amp; i).Value = 5 Then
  
      Range(&quot;d&quot; &amp; i).Value = &quot;=SUBTOTAL(9,R&quot; &amp; Kirilim2 + 1 &amp; &quot;C:R&quot; &amp; Kirilim3 &amp; &quot;C)&quot;
      Range(&quot;e&quot; &amp; i).Value = &quot;=SUBTOTAL(9,R&quot; &amp; Kirilim2 + 1 &amp; &quot;C:R&quot; &amp; Kirilim3 &amp; &quot;C)&quot;
      Range(&quot;f&quot; &amp; i).Value = &quot;=SUBTOTAL(9,R&quot; &amp; Kirilim2 + 1 &amp; &quot;C:R&quot; &amp; Kirilim3 &amp; &quot;C)&quot;
      Range(&quot;g&quot; &amp; i).Value = &quot;=SUBTOTAL(9,R&quot; &amp; Kirilim2 + 1 &amp; &quot;C:R&quot; &amp; Kirilim3 &amp; &quot;C)&quot;
      Range(&quot;h&quot; &amp; i).Value = &quot;=SUBTOTAL(9,R&quot; &amp; Kirilim2 + 1 &amp; &quot;C:R&quot; &amp; Kirilim3 &amp; &quot;C)&quot;
      Range(&quot;i&quot; &amp; i).Value = &quot;=SUBTOTAL(9,R&quot; &amp; Kirilim2 + 1 &amp; &quot;C:R&quot; &amp; Kirilim3 &amp; &quot;C)&quot;
      Range(&quot;j&quot; &amp; i).Value = &quot;=SUBTOTAL(9,R&quot; &amp; Kirilim2 + 1 &amp; &quot;C:R&quot; &amp; Kirilim3 &amp; &quot;C)&quot;
      
      
      Kirilim3 = Kirilim2
      Kirilim2 = Kirilim2 - 1
  End If
  
  If Range(&quot;B&quot; &amp; i).Value = 2 Then
  
  
  Range(&quot;d&quot; &amp; i).Value = &quot;=SUBTOTAL(9,R&quot; &amp; Kirilim2 + 1 &amp; &quot;C:R&quot; &amp; Kirilim1 + 1 &amp; &quot;C)&quot;
  Range(&quot;e&quot; &amp; i).Value = &quot;=SUBTOTAL(9,R&quot; &amp; Kirilim2 + 1 &amp; &quot;C:R&quot; &amp; Kirilim1 + 1 &amp; &quot;C)&quot;
  Range(&quot;f&quot; &amp; i).Value = &quot;=SUBTOTAL(9,R&quot; &amp; Kirilim2 + 1 &amp; &quot;C:R&quot; &amp; Kirilim1 + 1 &amp; &quot;C)&quot;
  Range(&quot;g&quot; &amp; i).Value = &quot;=SUBTOTAL(9,R&quot; &amp; Kirilim2 + 1 &amp; &quot;C:R&quot; &amp; Kirilim1 + 1 &amp; &quot;C)&quot;
  Range(&quot;h&quot; &amp; i).Value = &quot;=SUBTOTAL(9,R&quot; &amp; Kirilim2 + 1 &amp; &quot;C:R&quot; &amp; Kirilim1 + 1 &amp; &quot;C)&quot;
  Range(&quot;i&quot; &amp; i).Value = &quot;=SUBTOTAL(9,R&quot; &amp; Kirilim2 + 1 &amp; &quot;C:R&quot; &amp; Kirilim1 + 1 &amp; &quot;C)&quot;
  Range(&quot;j&quot; &amp; i).Value = &quot;=SUBTOTAL(9,R&quot; &amp; Kirilim2 + 1 &amp; &quot;C:R&quot; &amp; Kirilim1 + 1 &amp; &quot;C)&quot;
  
                    
      Kirilim3 = i - 1
      Kirilim2 = i - 1
      Kirilim1 = i - 1
  
  End If
  
  Next i
  
  
  End Sub
  </code></pre>