Finding and Selecting the Last Nonzero Cell in a Column

Hi All,

This is my first post so please excuse any violation of forum rules. I will be happy to amend if so advised.

I putting together a spreadsheet that applies Payments (as they come in) against the oldest open invoice. As payments come in, old invoices are closed out. An aging is done and late fees are applied.

I have made a macro that inputs all of the information of the invoice in a list. However, when a payment comes in, I am trying to write a macro that:

1) in column G - Finds the first non-zero balance
2) Applies the payment amount to that open invoice. If there is additional funds left over after the satisfaction of that invoice, I would like the payment to be applied to the next open invoice until all the funds of the payment have been drained.

Can someone please help me with this. I am having so much trouble trying to even just locate the first nonzero balance and select it.

Thank you in advance. Spreadsheet help.xlsm

Please someone help. I have a project due today and I'm trying my best to research some code to get this done.

from where you will catch values??

Do you want to assign it to Inv no & aircraft ID or other way??

Hi

Check attachment, I have created new macro. You need to fill Inv no. & Amt. received, rest will be taken by Macro.

Cheers!!

Excelliot!

Thank you very much for responding and for the code. I think I did a bad job of describing what was to be done by the macro. On the "Aero Ways" tab, the user is to input all the information directly from an invoice (not to pull from a vlookup search).

That information was to prepopulate the row on the "Entry Fields" tab. Then a Macro was to use the amount paid to search for the oldest outstanding invoice and apply that amount paid until it is exhausted.

I am going to try to play around with the code you sent to see if I can get the macro to do what I mentioned above.

Thank you once again for your help. It is great knowing there is such a responsive resource available to people who are in need of help!

Cheers!!

Do let me know if you need further assistance..

Excelliot

I am sorry, I thought modifying the code would be simple, but I can't seem to get it to do what I want. I have created a macro - "PaymentEntry" (that is where I need the help). When that is running, it prompts the user for a payment amount.

The user then enters the payment amount. Then the macro finds the first nonzero balance using the find feature. Then the macro applies the payment to the remaining invoice balance. I know how to write the code for the situation when the remaining balance is greater than the payment amount, but I do not know how to write the macro for the situation when the payment amount is greater than the first remaining invoice balance.

My problem is that I cannot figure out how to get the macro to apply the payment amount to the next nonzero remaining invoice balance after zeroing out the first nonzero remaining invoice balance. How can I get the macro to apply the payment to the first remaining invoice amount, zero it out, then apply the remaining balance of the payment to the next remaining invoice balance, and again and again until all of the payment amount has been satisfied.

Sorry to trouble you with this again, but I would appreciate your help. Please find my latest spreadsheet attached. Spreadsheet.xlsm

The macro is called EnterAmount, not PaymentEntry

Hi

No Issues.. I am happy that you are willing to learn...

Check this attachment, i have modified mine code & your code too match your requirements...

Button 1 is for your code & button 2 is for my code...

Happy Learning..

Cheers!!

Excelliot,

Thank you so much. That is exactly what I was looking for. Your code makes it look so easy and concise. My method for even finding the first nonzero value was not nearly as efficient as the few lines of code you used. Well I am learning and hopefully getting better at this vba coding. Thank you for your help again!

Excelliot,

I have one last question for you regarding the spreadsheet. I have taken it and added a lot to it, but there is one aspect of the spreadsheet I am not able to figure out just yet.

Background: Everytime a payment is made, it is used to pay off the interest first and then to deduct from the Invoice Balance (principal balance). However, if a payment does not pay off the late fees entirely, then that payment is only offset against the late fee.

The problem I am having is that when a payment less than the total interest is made, I need the late fee cell to stay as a formula but to simply deduct the payment. so for example (e2 x j2) - (payment). And it further can be compounded by a subsequent payment that is yet still less than the total interest amount. In that situation, i would need to keep track of the sum of all the payments that are not able to pay off the late fee. So that formula would look like (e2 x j2) - (sum(payment 1 and 2)).

I don't know if there is anyway to do this, but I thought I would see to bug you one last time.

Thank you in advance.

Sorry forgot to include the attachment. spreadsheet help - Payment Assignment Spreadsheet.xlsm

Hi

replace line

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Cheers!!