FIFO Based Receipt Adjustment To determine date when particular invoice is finally cleared

[spremkishan]

Hi,

I am trying to write a macro for getting payment date against each invoice.

Eg:
Invoice Date Inv Value Customer Code Inv_No
1 Jan 15 100,0000 1 1

Payment Detials of same customer
payment Date Amt Customer code Paymentref
2 Jan 15 950000 1 1
5 Jan 15 5000 1 2

In this case i want against first invoice clearing date as 5 jan 2015(since that is the date when the entire amount is cleared.)

I have two sheets one sheet has Invoice details for all customers and other sheet has payment details for all customers.

Logic: I want VBA to read each payment line item and then compare whether it belongs to the same customer and if it is then it will compare whether payment value is greater than invoice value if it is TRUE then it will allocate current payment date as clearing date to the invoice selected. Else it will calculate inv_bal and looping will be done to achieve clearing date of each line.

Here is the code

Please Login or Register  to view this content.

Please help me out, it is not working and throwing Run time error '6' Overflow.


Regards
Prem

[excelliot]

hi

you need to Add your code between [CODE ]Please Login or Register to view this content.[/CODE] as per forum rule..(Remove spaces between E ]while posting your code)

Do the needful to get response..

Cheers!!

[excelliot]

Hi

check this attachment..maintain data as per two sheet & click on blue button to run code:

updated code is:

Please Login or Register  to view this content.

Cheers!!

[spremkishan]

Hi Elliot,

Thanks a ton for your logic, it helped me a lot and saved me from sleepless nights.

Regards
Prem.

[excelliot]

Hi Elliot,

Thanks a ton for your logic, it helped me a lot and saved me from sleepless nights.

Regards
Prem.

Great.. mark this as solved.

[excelliot]

Hi

Use this updated code:

Please Login or Register  to view this content.

Cheers!!

[spremkishan]

Elloit,

Logic provided is not working as desired.

I have sorted file of invoice and payments based on customer and then oldest to newest.
Now, logic should be in such a way that it will read every payment line, and then it should read first invoice line if customer in payment line is matching with customer as per invoice file then it should compare whether payment amount is greater than zero in that case it should allot current payment line date as clearing date in invoice file. Now it should calculate payment balance and then proceed to next invoice
In case payment amt is less than invoice amt. it should calculate invoice balance and proceed to next payment.

Thanks for helping!!.

Regards
Prem.

[excelliot]

Elloit,

Logic provided is not working as desired.

I have sorted file of invoice and payments based on customer and then oldest to newest.
Now, logic should be in such a way that it will read every payment line, and then it should read first invoice line if customer in payment line is matching with customer as per invoice file then it should compare whether payment amount is greater than zero in that case it should allot current payment line date as clearing date in invoice file. Now it should calculate payment balance and then proceed to next invoice
In case payment amt is less than invoice amt. it should calculate invoice balance and proceed to next payment.

Thanks for helping!!.

Regards
Prem.

upload sample file, with new code this should work.

[spremkishan]

Elloit,

Here is the Sample File and code below

Please Login or Register  to view this content.

[excelliot]

where is customer no??

[spremkishan]

where is customer no??

Column A In Both Invoice and Payments file.

Regards
Prem.

[excelliot]

Hi

check this attachment & let me know..

Cheers!!

[sparaguak]

Hello!!!

This FIFO_Logic.xlsm does exactly what i was looking for!!!!! i downloaded the file and tested it for hours before i use it.
Finally i found a small bug that i THINK it has to do with decimals (on integers it works great). I WISH i could send u an attachment that u could see the problem, but i really cant make it for some reason , i got very confused with this
Can anyone help me on how to send an attachment that i have a small example with the problem?

Thank you in advance and i am really sorry, its my first day here

[JeteMc]

Administrative Note:

Hello sparaguak Welcome to Excel Forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

As to sending an attachment, the paperclip icon does not work for this website so once you have started your new thread please follow the instructions in the banner at the top of the page that begins "sparaguak Fast answers need clear examples..."

Let us know if you have any questions.