Balance from one column added to a cell in the next column help.

E

E. Jones

Board Regular
Joined
Oct 21, 2012
Messages
59
Hello all, and as usual, thank you for the help in advance!

I want to pay off a list of expenses over a certain number of months using the same amount of money each month. Once one expense is paid off, I want to allocate the money that was going to that expense to the next expense, and so on. Eventually, the total sum of what was being paid to the lessor expenses will be allocated to the most expensive one.


Hopefully this chart helps to illustrate what I am asking. (Row 6 is a space between months, and the next month begins in row 7)


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD="align: center"]Expense 1[/TD]
[TD="align: center"]Expense 2[/TD]
[TD="align: center"]Expense 3[/TD]
[TD="align: center"]Expense 4[/TD]
[TD="align: center"]TOTAL[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Beginning balance[/TD]
[TD]$25[/TD]
[TD]$175[/TD]
[TD]$200[/TD]
[TD]$250[/TD]
[TD]$650[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Minimum payment[/TD]
[TD]$25[/TD]
[TD]$45[/TD]
[TD]$30[/TD]
[TD]$15[/TD]
[TD]$115[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Payment I should make[/TD]
[TD]$25[/TD]
[TD]$45[/TD]
[TD]$30[/TD]
[TD]$15[/TD]
[TD]$115[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]New Balance[/TD]
[TD]$0[/TD]
[TD]$130[/TD]
[TD]$170[/TD]
[TD]$235[/TD]
[TD]$535[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Beginning balance[/TD]
[TD]$0[/TD]
[TD]$130[/TD]
[TD]$170[/TD]
[TD]$235[/TD]
[TD]$535[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Minimum payment[/TD]
[TD]$0
[/TD]
[TD]$45[/TD]
[TD]$30[/TD]
[TD]$15[/TD]
[TD]$90[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Payment I should make[/TD]
[TD]$0[/TD]
[TD]$70[/TD]
[TD]$30[/TD]
[TD]$15[/TD]
[TD]$115[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]New Balance[/TD]
[TD]$0[/TD]
[TD]$60[/TD]
[TD]$140[/TD]
[TD]$220[/TD]
[TD]$420[/TD]
[/TR]
</tbody>[/TABLE]

What I need is a formula that will automatically adjust how much of a payment I should make for expense 2 (B9), now that expense 1 is paid off. I still want the total of the payments to be the same $115, but the $25 I paid for expense 1 is now available to be added to expense 2 for a total payment of $70 paid the next month, leaving a balance of $60.

The total minimum payments for expense 1 and 2 is $70, but now that the balance for expense 2 is only $60, I want to pay it off, leaving a balance of $10 that can be added to the minimum payment of expense 3, making that payment $40 instead of $30.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
E. Jones,
Here is a macro solution (See macro code below). I inserted a row at the very top of your data to make room for a title. So for this macro to run properly, your data must be in the range A2:F6 as I have shown in the attached image.
I created a 'Make Payments' Form Control Button to run the macro, but you can also run it using the Alt + F8 >select the macro named 'Test' from 'This Workbook'>then select 'Run'.

Sheet2

ABCDEFGH
(1st button press)
Beginning Balance
Payment Made(2nd button press)
New Balance
Beginning Balance
Payment Made(3rd button press)
New Balance
Beginning Balance
Payment Made(4th button press)
New Balance
Beginning Balance
Payment Made(5 button press)
New Balance

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 121px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="align: center"]Expense 1[/TD]
[TD="align: center"]Expense 2[/TD]
[TD="align: center"]Expense 3[/TD]
[TD="align: center"]Expense 4[/TD]
[TD="align: center"]TOTAL[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: left"]Beginning balance[/TD]
[TD="align: left"]$25 [/TD]
[TD="align: left"]$175 [/TD]
[TD="align: left"]$200 [/TD]
[TD="align: left"]$250 [/TD]
[TD="align: left"]$650 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: left"]Minimum payment[/TD]
[TD="align: left"]$25 [/TD]
[TD="align: left"]$45 [/TD]
[TD="align: left"]$30 [/TD]
[TD="align: left"]$15 [/TD]
[TD="align: left"]$115 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: left"]Payment I should make[/TD]
[TD="align: left"]$25 [/TD]
[TD="align: left"]$45 [/TD]
[TD="align: left"]$30 [/TD]
[TD="align: left"]$15 [/TD]
[TD="align: left"]$115 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: left"]New Balance[/TD]
[TD="align: left"]$0 [/TD]
[TD="align: left"]$130 [/TD]
[TD="align: left"]$170 [/TD]
[TD="align: left"]$235 [/TD]
[TD="align: left"]$535 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: left"]Beginning Balance[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]235[/TD]
[TD="align: right"]535[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]
[TD="align: left"]Payment Made[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]115[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]
[TD="align: left"]New Balance[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]420[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]12[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]420[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]115[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]14[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]305[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]16[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]305[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]17[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]115[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]18[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]190[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]19[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]20[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]190[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]21[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]115[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]22[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]23[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]24[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]25[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]26[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the below code into the code window that just opened up. Then Close the VBA window. That's it.... you are done. If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

So there you have it. As it is written you used 4 Expense columns and a Total column. The code would need to be modified if you add or remove Expense columns. Perhaps a future improvement.

To add a Form Control Button to an Excel Worksheet:
You need the Developer Tab in the ribbon at the top of the sheet activated to do this. If you need this installed...
1st - Click on the Windows icon, the 4 colored boxes in the circle at the top lefthand of the screen, next to 'Home'. At the bottom of that tab click on 'Excel Options', then click on the 'Popular', then 'Show Developer tab in the Ribbon'. Then click 'OK' at the bottom of that tab. You will notice that the 'Developer' tab has been added to the ribbon. Now save your workbook.

For this next part you need to have the macro installed as descibed above.
2nd - Select the sheet where the button will go. Left click on the Developer tab. In the middle of that tab is an icon labled 'Insert'. Click on that icon and you will see 2 sets of controls, you want the 'Form Control' group, hover the cursor over the icon that looks like a rectangular button. It should say 'Button - (Form Control)'. Click on that icon, then down where you want the top LH corner to be, left click, hold and drag down to where you want the lower RH corner. The button is labled 'Button 1' by default. You can change that later, but first you should be promted to select a macro to assign to the button. Select 'Test' from 'This Workbook'. You can now right click on the 'Button 1' name and you can change the name to 'Make Payments' or whatever. Then click outside the button and save your workbook again. You are now ready to use the button.
Good luck!
Perpa

Code: 
Sub Test()
Dim AmtRem, Mymin As Double
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row + 2
Cells(LastRow, "A") = "Beginning Balance"
Cells(LastRow + 1, "A") = "Payment Made"
Cells(LastRow + 2, "A") = "New Balance"
Cells(LastRow, "B") = Cells(LastRow - 2, "B")
Cells(LastRow, "C") = Cells(LastRow - 2, "C")
Cells(LastRow, "D") = Cells(LastRow - 2, "D")
Cells(LastRow, "E") = Cells(LastRow - 2, "E")
Cells(LastRow, "F") = Cells(LastRow, "B") + Cells(LastRow, "C") + Cells(LastRow, "D") + Cells(LastRow, "E")
AmtRem = 0
For col = 2 To 5
If Cells(LastRow, col) = 0 Then
    Cells(LastRow + 2, col) = 0
    GoTo Passem
End If
If Cells(LastRow, col) > 0 Then
    If col = 2 And Cells(LastRow, col) <> 0 Then
        Mymin = Cells(4, col)
        GoTo 100
    End If
    
    If col = 3 And Cells(LastRow, col - 1) = 0 Then
        Mymin = Cells(4, col) + Cells(4, col - 1)
        GoTo 100
    End If
    
    If col = 4 And Cells(LastRow, col - 1) = 0 Then
        Mymin = Cells(4, 2) + Cells(4, 3) + Cells(4, 4)
        GoTo 100
    ElseIf col = 4 And Cells(LastRow, col - 1) <> 0 Then
        Mymin = Cells(4, col)
        GoTo 100
    End If
    
    If col = 5 And Cells(LastRow, col - 1) = 0 Then
        Mymin = Cells(4, 2) + Cells(4, 3) + Cells(4, 4) + Cells(4, 5)
        GoTo 100
    ElseIf col = 5 And Cells(LastRow, col - 1) <> 0 Then
        Mymin = Cells(5, col)
    End If
100:
    Cells(LastRow + 2, col) = Cells(LastRow, col) - Mymin + AmtRem
    AmtRem = 0
End If
If Cells(LastRow + 2, col) < 0 Then
       AmtRem = Cells(LastRow + 2, col)
       Cells(LastRow + 2, col) = 0
       GoTo Passem
End If
Passem:
Next col
Cells(LastRow + 2, "F") = Cells(LastRow + 2, "B") + Cells(LastRow + 2, "C") + Cells(LastRow + 2, "D") + Cells(LastRow + 2, "E")
Cells(LastRow + 1, "B") = Cells(LastRow, "B") - Cells(LastRow + 2, "B")
Cells(LastRow + 1, "C") = Cells(LastRow, "C") - Cells(LastRow + 2, "C")
Cells(LastRow + 1, "D") = Cells(LastRow, "D") - Cells(LastRow + 2, "D")
Cells(LastRow + 1, "E") = Cells(LastRow, "E") - Cells(LastRow + 2, "E")
Cells(LastRow + 1, "F") = Cells(LastRow, "F") - Cells(LastRow + 2, "F")
End Sub
 
Upvote 0
Perpa

Thank you for your answer, the macro works as indicated, and solves the question I had!

Additional question:
What if instead of wanting to solve for a zero balance, I wanted to solve for a certain percentage of the beginning balance, say 25%, or $25 of a $100 balance? Is it possible to incorporate this macro for that solution?

Example: If my "new balance" is less than or equal to 25% of the beginning balance, how would I calculate to only make the minimum payment, (unless that minimum payment is equal to or greater than the remaining balance), and then have the leftover go towards the next expense?

I hope I explained that right.
 
Upvote 0
E. Jones,
If I understood correctly, the code below produced the following results:

Sheet2

ABCDEFGH
(1st button press)
Beginning Balance
Payment Made(2nd button press)
New Balance
Beginning Balance
Payment Made(3rd button press)
New Balance
Beginning Balance
Payment Made(4th button press)
New Balance
Beginning Balance
Payment Made(5th button press)
New Balance
Beginning Balance
Payment Made(6th button press)
New Balance
Beginning Balance
Payment Made(7th button press)
New Balance
Beginning Balance
Payment Made(8th button press)
New Balance
Beginning Balance
Payment Made(9th button press)
New Balance
Beginning Balance
Payment Made(10th button press)
New Balance

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 121px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: left"]Beginning Balance[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]235[/TD]
[TD="align: right"]535[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]
[TD="align: left"]Payment Made[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]115[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]
[TD="align: left"]New Balance[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]420[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]12[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]420[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]115[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]14[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]305[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]16[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]305[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]17[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]90[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]18[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]215[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]19[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]20[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]215[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]21[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]45[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]22[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]170[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]23[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]24[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]170[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]25[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]26[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]125[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]27[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]28[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]125[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]29[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]30[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]31[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]32[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]33[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]34[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]35[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]35[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]36[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]35[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]37[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]38[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]39[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]40[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]41[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]42[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]43[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]44[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]45[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]46[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Here is the revised code:
Perpa

Code: 
Sub Test()
Dim AmtRem, Mymin As Double
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row + 2
Cells(LastRow, "A") = "Beginning Balance"
Cells(LastRow + 1, "A") = "Payment Made"
Cells(LastRow + 2, "A") = "New Balance"
Cells(LastRow, "B") = Cells(LastRow - 2, "B")
Cells(LastRow, "C") = Cells(LastRow - 2, "C")
Cells(LastRow, "D") = Cells(LastRow - 2, "D")
Cells(LastRow, "E") = Cells(LastRow - 2, "E")
Cells(LastRow, "F") = Cells(LastRow, "B") + Cells(LastRow, "C") + Cells(LastRow, "D") + Cells(LastRow, "E")
AmtRem = 0
For col = 2 To 5
    If Cells(LastRow, col) = 0 Then    'Expense has been paid off
        Cells(LastRow + 2, col) = 0
        Cells(LastRow + 1, col) = 0
        GoTo Nxt_Col
    End If
    If Cells(LastRow, col) > 0 Then
        If col = 2 Then
            Mymin = Cells(4, col)
            GoTo 100
        End If
        If col = 3 And Cells(LastRow, col - 1) = 0 And Cells(LastRow, col) > Cells(3, col) * 0.25 Then
            Mymin = Cells(4, col) + Cells(4, col - 1)
            GoTo 100
        ElseIf col = 3 And Cells(LastRow, col - 1) = 0 And Cells(LastRow, col) <= Cells(3, col) * 0.25 Then
             Mymin = Cells(4, col)
            GoTo 100
        End If
    
        If col = 4 And Cells(LastRow, col - 1) = 0 And Cells(LastRow, col) > Cells(3, col) * 0.25 Then
            Mymin = Cells(4, col - 1) + Cells(4, col)
            GoTo 100
        ElseIf col = 4 And Cells(LastRow, col - 1) = 0 And Cells(LastRow, col) <= Cells(3, col) * 0.25 Then
            Mymin = Cells(4, col)
            GoTo 100
        ElseIf col = 4 And Cells(LastRow, col - 1) <> 0 And Cells(LastRow, col) <= Cells(3, col) * 0.25 Then
            Mymin = Cells(4, col)
            GoTo 100
        ElseIf col = 4 And Cells(LastRow, col - 1) <> 0 And Cells(LastRow, col) > Cells(3, col) * 0.25 Then
            Mymin = Cells(4, col)
            GoTo 100
        End If
    
        If col = 5 And Cells(LastRow, col - 1) = 0 And Cells(LastRow, col) > Cells(3, col) * 0.25 Then
            Mymin = Cells(4, col - 1) + Cells(4, col)
            GoTo 100
        ElseIf col = 5 And Cells(LastRow, col - 1) = 0 And Cells(LastRow, col) <= Cells(3, col) * 0.25 Then
            Mymin = Cells(4, col)
            GoTo 100
        ElseIf col = 5 And Cells(LastRow, col - 1) <> 0 And Cells(LastRow, col) <= Cells(3, col) * 0.25 Then
            Mymin = Cells(5, col)
            GoTo 100
        ElseIf col = 5 And Cells(LastRow, col - 1) <> 0 And Cells(LastRow, col) > Cells(3, col) * 0.25 Then
            Mymin = Cells(4, col)
        End If
100:
        Cells(LastRow + 1, col) = Mymin + Abs(AmtRem)
        Cells(LastRow + 2, col) = Cells(LastRow, col) - Mymin + AmtRem
        AmtRem = 0
    End If
    If Cells(LastRow + 2, col) < 0 Then
         AmtRem = Cells(LastRow + 2, col)
         Cells(LastRow + 2, col) = 0
         Cells(LastRow + 1, col) = Cells(LastRow, col) - Cells(LastRow + 2, col)
    End If
Nxt_Col:
Next col
Cells(LastRow + 2, "F") = Cells(LastRow + 2, "B") + Cells(LastRow + 2, "C") + Cells(LastRow + 2, "D") + Cells(LastRow + 2, "E")
Cells(LastRow + 1, "F") = Cells(LastRow, "F") - Cells(LastRow + 2, "F")
End Sub
 
Upvote 0
Perpa

Thanks a bunch for continuing to answer my questions with working solutions. Yes, this does get me closer to what I'm trying to accomplish, and I definitely appreciate all of your input. I will try to "finish" it using what you have given me as very solid information to guide me in the right direction.
 
Upvote 0
E. Jones said:
Perpa

Thanks a bunch for continuing to answer my questions with working solutions. Yes, this does get me closer to what I'm trying to accomplish, and I definitely appreciate all of your input. I will try to "finish" it using what you have given me as very solid information to guide me in the right direction.
Click to expand...

E,
Glad to hear this news. Thanks for the feedback, and you are quite welcome.
Perpa
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Calculate Total Expense With Condition
2
Replies
10
Views
511
Cubist
Cubist
A
  • Question Question
Daily Interest Loan Scheduler | Missed Payments
Replies
0
Views
241
aurelius33
A
M
  • Question Question
Summing end of month account balances based on percentages that vary line by line
Replies
3
Views
215
SanjayGMusafir
SanjayGMusafir
A
  • Question Question
Create report by matching part of item and populate for each cell under item across sheets
Replies
2
Views
217
Ali M
A
M
  • Solved
Add item in first digits with create space after it based on sheet name
Replies
8
Views
130
kvsrinivasamurthy
K

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top