Shorten Loop Help

[dfeteau]

I created a loop that works. Problem is that it needs to go across about 66 columns and then go down about 2000+ lines. I think there has got to be a way to make it go faster but I'm not seeing it. Anyone have any suggestions?

Quick Notes: It starts in Column A. It needs to apply the same payment amount going across until the balance is less than the payment and then start putting zeros.

Do Until ActiveCell.Value = ""
    pymt = ActiveCell.Offset(0, 3).Value
    balance = ActiveCell.Offset(0, 4).Value
    c = 5
    Do Until c = 71
        If balance > pymt Then
            ActiveCell.Offset(0, c).Value = pymt
            c = c + 1
            balance = balance - pymt
        ElseIf balance = 0 Then
            ActiveCell.Offset(0, c).Value = 0
            c = c + 1
        Else
            ActiveCell.Offset(0, c).Value = balance
            balance = 0
            c = c + 1
        End If
    Loop
    ActiveCell.Offset(1, 0).Select
Loop

Thanks

 

[stnkynts]

I am not sure if i got your layout set up correctly but if I do then this should work lickety split.

Sub CalculateBalances()
Dim lastrow As Long, iRow As Long, iCol As Long
Dim Payment As Long, lastcol As Long
Application.CutCopyMode = False
lastrow = Range("A" & Rows.Count).End(xlUp).Row
For iRow = 2 To lastrow
    Payment = Range("D" & iRow).Value
        For iCol = 5 To 66
            lastcol = Cells(iRow, Columns.Count).End(xlToLeft).Column
            If Cells(iRow, lastcol).Value < Payment Then
                Cells(iRow, lastcol).Value = 0
                Exit For
            Else
                Cells(iRow, iCol + 1).Value = Cells(iRow, iCol).Value - Payment
            End If
        Next iCol
Next iRow
Application.CutCopyMode = True
 
End Sub

 

[wigi]

Hello there,

I'm not sure I understand why you make this so complicated for the code. In addition, all the select's obviously make this very slow, next to all the (big) loops.

Why not using what Excel's very good at - formulas? And this combined with the power of VBA to take away the manual steps.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">payment</td><td style=";">balance</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">ID 1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">9500</td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">500</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">ID 2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">500</td><td style="text-align: right;;"></td><td style="text-align: right;;">500</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">ID 3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">10000</td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">ID 4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">10000</td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">ID 5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">10000</td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">ID 6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">10000</td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">ID 7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">10000</td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">ID 8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">10000</td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">ID 9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">10000</td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">ID 10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">10000</td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:0,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Then, this will work flawlessly:

Sub Wigi()

    With Range(ActiveCell, ActiveCell.End(xlDown)).Offset(, 6).Resize(, 66)
        .Formula = "=MAX(0,MIN(RC4,RC5-SUM(RC6:RC[-1])))"
        .Value = .Value
    End With

End Sub