Need macro to allocate cash receipts over previous customer debts

[Vasim]

Hi.

I have a huge data in excel like the one in the image link below:

Sample_tgg2xp.jpg

I need to know which part of the receipt 450.00 (cell C8) belongs to D3, D4, D5 cells and etc. beginning from the first non-zero cell in D column, so that to find currency difference effect within 450.00 (C8) like the following: (200.00)*(1.80-1.98)+(450.00-200.00)*(1.86-1.98) or if we have 50.00 instead of 450.00 just to make calculation like this: (50.00)*(1.80-1.98).

I just can't imagine how to do it using formulas and I don't know VBA. So it is very much appreciated if anyone can help me with this issue either via excel formulas or macro.

[danny2000]

Vasim.

You're really going to be a little bit clearer than this. I've just spent an hour trying to figure this out and can't make any sense of your question.

Upload a small example of what you want and explain in that. Remember, the person helping you will have no idea about the internal workings of this calculation. The example is confusing.

I started something here but can't make sense of the question enough to continue.

Please Login or Register  to view this content.

[Vasim]

Vasim.

You're really going to be a little bit clearer than this. I've just spent an hour trying to figure this out and can't make any sense of your question.

Upload a small example of what you want and explain in that. Remember, the person helping you will have no idea about the internal workings of this calculation. The example is confusing.

I started something here but can't make sense of the question enough to continue.

Please Login or Register  to view this content.

I'm very sorry about this, my English is not good enough. I can explain the process. I sell some goods to one customer in local currency (LC), but these goods were purchased by me from foreign supplier in foreign currency (FC), for example in USD. As I need to pay for the goods in FC I need to exchange the LC received from the customer into FC. And the problem is that exchange rates significantly vary everyday and the customer can buy goods 5-6 times or even more and then make some part of payment. So I need excel to check for the balances of transactions and find the first non-zero balance (I mean D3 because sales were not paid and the customers debt balance is 200.00) and then check the payment (C8 which is 450.00) and if the payment is greater than 200.00, deduct 200 from 450 and calculate difference between x-rate of D3 transaction and C8 transaction (I mean, 200*(1.80-1.98)) and then do the same thing for the next transactions' balances (D4, D5 ...) until 450 becomes zero.

I uploaded excel file and did calculations manually so that you could have better understanding of the issue. Below is the link:

https://app.box.com/s/vgrquadusid56h4ciyf67ncxpiu390ug