VB Code to calculate amount from transfer of inventory

[rizmomin]

Hello:

Please refer to attached file.
I have data as shown.
This data shows inventory transfer from one store to another.
Column B and C are Store name.
Column G is Qty transfer and column H is Unot cost and Column I is total cost.

I need VB Code to do following

I need to create Invoice for each store for what they owe to each other.
My logic here is as below.

1st sort out the data for same 2 stores transfer (from and to, To and From).

Example
Row#2 and 3 shows Transfer from BN to WE (Cost is $6 and $14.40 = $20.40)
Now look for any transfer from WE to BN
Row#25 and #27 shows transfer from WE to BN (Cost is $21.60 and $42 = $63.60).

So now i would need output in sheet2 as shown

Let me know if you have any questions.
Thanks.

Riz

[rizmomin]

Hello:

Please refer to attached file.

I have shown the output needed as shown in sheet3 Let me know if you have any questions.
Thanks.

R

[karedog]

Maybe :

Please Login or Register  to view this content.

[rizmomin]

Hello Karedog:

Great Job, Thanks but i am seeing small issue.
Let me explain.
Inventory is transfered FROM and TO stores
So inventory moving FROM store gets $$ from TO Store

So refer to Sheet4 which was created by your code.
Here look at the data in Row 10 & 11
In this case 2 item # inventory is moved from EL to WE, totalling $15
SO in this case WE needs to pay EL as i have highlighted.

Go down to data from row 24 down.
Here Row# 26 thru 36 : Inventory is moved from LJ to WE totalling $106.
Now Row#37 shows : Inventory moved from WE to LJ totalling $6.
So in this case, WE will pay $100

Go down Row 58
Inventory moved FROM SO to CW, so CW to pay SO $3.00 and so on.


SO now once this is done, if code can be modified to give me summary as shown in column L thru O.

Please let me know if you have any questions.
Thanks.

Riz

[karedog]

Hi Riz,

So you mean, you always want a positive number for total ?
Then use this modified code :

Please Login or Register  to view this content.

[rizmomin]

Hello karedog:

Seems like you got it.

SO now once this is done, if code can be modified to give me summary as shown in column L thru O.

Please refer to sheet5 of attached sheet.

Riz

[karedog]

Should be :

Please Login or Register  to view this content.

[rizmomin]

Hello Karedog:

Thanks a lot...this will work

R