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

**spremkishan** · 08-12-2015, 02:24 AM

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** · 08-12-2015, 02:42 AM

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** · 08-12-2015, 11:20 AM

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** · 08-12-2015, 12:25 PM

Hi Elliot,

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

Regards
Prem.

**excelliot** · 08-12-2015, 01:36 PM

Originally Posted by spremkishan

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** · 08-15-2015, 05:09 AM

Hi

Use this updated code:

Please Login or Register  to view this content.

Cheers!!

**spremkishan** · 08-15-2015, 10:55 AM

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** · 08-15-2015, 11:46 AM

Originally Posted by 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.

upload sample file, with new code this should work.

**spremkishan** · 08-15-2015, 03:04 PM

Elloit,

Here is the Sample File and code below

Please Login or Register  to view this content.

**excelliot** · 08-16-2015, 03:19 AM

where is customer no??

**spremkishan** · 08-16-2015, 04:23 AM

Originally Posted by excelliot

where is customer no??

Column A In Both Invoice and Payments file.

Regards
Prem.

**excelliot** · 08-17-2015, 05:06 AM

Hi

check this attachment & let me know..

Cheers!!

**sparaguak** · 11-20-2019, 06:49 AM

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** · 11-22-2019, 08:27 PM

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.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

Re: FIFO Based Receipt Adjustment To determine date when particular invoice is finally cle

Re: FIFO Based Receipt Adjustment To determine date when particular invoice is finally cle

Re: FIFO Based Receipt Adjustment To determine date when particular invoice is finally cle

Re: FIFO Based Receipt Adjustment To determine date when particular invoice is finally cle

Re: FIFO Based Receipt Adjustment To determine date when particular invoice is finally cle

Re: FIFO Based Receipt Adjustment To determine date when particular invoice is finally cle

Re: FIFO Based Receipt Adjustment To determine date when particular invoice is finally cle

Re: FIFO Based Receipt Adjustment To determine date when particular invoice is finally cle

Re: FIFO Based Receipt Adjustment To determine date when particular invoice is finally cle

Re: FIFO_Logic.xlsm

Re: FIFO Based Receipt Adjustment To determine date when particular invoice is finally cle

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Invoice Description items based on start and end date

[SOLVED] Automatic invoice charges based on date selection

Formula to determine due date based on hire date

[SOLVED] Make vlookup reset back to Name or blank after invoice is saved and cleared

[SOLVED] Determine date based on request date meanwhile falls on particular days

A4 date of invoice - E4 invoice status - F4 Date invoice was paid - G4 number of days invo

Date of Receipt based on In/OUT

Bookmarks

Bookmarks

Posting Permissions