macro to create forward balances by deleting rows (and cells) and changing invoice numbers

Hey all,

Please see attached spreadsheet for examples.
In this workbook, I have purposely made all the payments (Column I) not quite offset the sales amounts (Column H)
I need a macro that will look at the subtotaled due bill amount, and apply a forward balance to the customer.
Since I’m not quite sure how to put this into words, I’ll describe exactly what I’m hoping the macro will do to the example spreadsheet:

• Row 7 is -$0.01, so delete rows 2 through 5, change cell H6 to 0 and change cell I6 to 0.01. Add an “FB” at the end of inv# 10550 in cell G6 (FB for Forward Balance)
• Row 11 is $20.00. Since it’s less than the last invoice for that customer, apply a forward balance to it by: Deleting rows 8 and 9, change cell I10 to zero, change cell H 10 to $20.00 and add an FB to the end of inv# 25779 in cell G10.
• Row 14 is -$10.00. Delete row 12, change cell H13 to zero and change cell I13 to $10.00. Add an “FB” to the end of inv# 98779 in cell G13
• Row 24 is -$1.36, so delete rows 15 through 22. Change cell H23 to zero and change cell I23 to $1.36. Add an “FB” at the end of invoice # 11120 in cell G23
• Row 28 is $2.00, so delete rows 25 and 26, change cell I27 to zero and change cell H27 to $2.00. Add an “FB” to the end of inv# 10103 in cell G27.

Please let me know if any additional clarification is required.

forward balances.xlsm

I can't even describe how much easier you've made my life lol
Thanks so much Xladept, I'll give it a spin at work tomorrow

Hey Xladept,

I gave it a go today, but with no luck.

it gave me the following error:

Run-time error '13':
Type mismatch

And when I debugged it highlighted the following line of code:

Please Login or Register  to view this content.

I'm not sure what's wrong, because it worked fine with the sample spreadsheet.
I'm using a large spreadsheet now where i use the previous macro you made for me, and then run this one afterwards. Maybe it doesn't like having invoices that actually offset?

Can you help?

I'll post one bright and early tomorrow. I don't have one at home unfortunately

Ok…so it turns out I’m dumb and forgot to change r to r=7 (had it set to r=1)
So this time it ran and it worked..sort of.
The problem is it turned everything into a forward balance, and something else went wrong because the grand total changed.
If it’s possible, I’d like it to only create a FB under the following condition:

• If the Cust # Subtotal in column J is less than the Sales amount of the invoice just above it in column H (the most current one)

The way you had it make the FB was perfect, a positive FB should be in the Sales amount column, and a negative FB should be in the payments column.
Thanks again!

Assume I've run the below routine before going to use the FB:

Please Login or Register  to view this content.

sorry, forgot to attach the file, here you go:

due bill macro2.xlsx

I gave it a try but it turns all invoices into FBs.
If the total for the customer is less than the final invoice, than it should create a FB, if not then it should do nothing.

does that answer your question about 0 forward balances?