title: Account Receivable Aging with Excel VBA
body: <p>I have created a vba code to calculate age wise report of Account Receivable as seen in below image. But It is taking too long time to complete calculation.
  I would appreciate if you would help me optimize this code to make calculation time fast.</p>
  
  <p>Excel Sheet looks like this:</p>
  
  <p><a href="https://i.stack.imgur.com/KUyE2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KUyE2.png" alt="enter image description here"></a>
  My Code is as under:</p>
  
  <pre><code>Sub ARAgingSummary()
      Dim StartRow As Long
      Dim TotalDue As Double
      Dim BalanceDue As Double
      Dim L30 As Double
      Dim L60 As Double
      Dim L90 As Double
      Dim L120 As Double
      Dim O120 As Double
      Dim OpeningBal As Double
  
      Dim AL30 As Range
      Dim AL60 As Range
      Dim AL90 As Range
      Dim AL120 As Range
      Dim AO120 As Range
      Dim AOpeningBal As Range
  
      StartRow = 5
      Do Until Range("I" &amp; StartRow) = ""
  
          Do Until Range("I" &amp; StartRow) = ""
  
              TotalDue = Range("I" &amp; StartRow).Value
              L30 = Range("B" &amp; StartRow).Value
              L60 = Range("C" &amp; StartRow).Value
              L90 = Range("D" &amp; StartRow).Value
              L120 = Range("E" &amp; StartRow).Value
              O120 = Range("F" &amp; StartRow).Value
              OpeningBal = Range("G" &amp; StartRow).Value
  
              Set AL30 = Range("J" &amp; StartRow)
              Set AL60 = Range("K" &amp; StartRow)
              Set AL90 = Range("L" &amp; StartRow)
              Set AL120 = Range("M" &amp; StartRow)
              Set AO120 = Range("N" &amp; StartRow)
              Set AOpeningBal = Range("O" &amp; StartRow)
  
              If L30 &lt;= TotalDue Then
                  AL30.Value = L30
                  BalanceDue = TotalDue - L30
              ElseIf L30 &gt; TotalDue Then
                  AL30.Value = TotalDue
                  Exit Do
              End If
  
              If L60 &lt;= BalanceDue Then
                  AL60.Value = L60
                  BalanceDue = BalanceDue - L60
              ElseIf L60 &gt; BalanceDue Then
                  AL60.Value = BalanceDue
                  Exit Do
              End If
  
              If L90 &lt;= BalanceDue Then
                  AL90.Value = L90
                  BalanceDue = BalanceDue - L90
              ElseIf L90 &gt; BalanceDue Then
                  AL90.Value = BalanceDue
                  Exit Do
              End If
  
              If L120 &lt;= BalanceDue Then
                  AL120.Value = L120
                  BalanceDue = BalanceDue - L120
              ElseIf L120 &gt; BalanceDue Then
                  AL120.Value = BalanceDue
                  Exit Do
              End If
  
              If O120 &lt;= BalanceDue Then
                  AO120.Value = O120
                  BalanceDue = BalanceDue - O120
              ElseIf O120 &gt; BalanceDue Then
                  AO120.Value = BalanceDue
                  Exit Do
              End If
  
              If OpeningBal &lt;= BalanceDue Then
                  AOpeningBal.Value = OpeningBal
                  BalanceDue = BalanceDue - OpeningBal
                  Exit Do
              ElseIf OpeningBal &gt; BalanceDue Then
                  AOpeningBal.Value = BalanceDue
                  Exit Do
              End If
  
          Loop
          BalanceDue = 0
          StartRow = StartRow + 1
      Loop
  End Sub
  </code></pre>
  
  <p>This is working fine as far as calculation is concern but calculation time is so long.</p>