Hi

I am a novice as far as VB is concerned. But I have tried to write some code. The sheet containing the Code and the required output are attached.

I need to find the age of my Inventory on a given date. The following steps need to be coded correctly.

1- all the transactions sales and purchases are on one sheet. - AgeReport
2- The data is first sorted by SecID and then by Settlement date.
3. VB code that does the following, given the Report date on the Age Report Sheet in Cell B1.
a - For each SecID add all purchases for Settlement Date< Report date
b - For each SecID add all sales for Settlement Date< Report date
c - subtract total sales from total purchases ; say CurrInv
g - now come to the last transaction with the SecID and compare its Purchase Amt against CurrInv
h - if Purchase Amt is > CurrInv, then this is the only txn from which securities are left in the CurrInv. Write the CurrInv Amt in Column F;
ELSE if Purchase Amt = CurrInv, then all securities from this purchase are left over;
ELSE if CurrInv > Purchase Amt , then add the transaction prior to it and check against the CurrInv. where sum of Purchase Amt becomes >= CurrInv, upto that point going backwards from last Row with SecID towards the first Row containing the SecID, all txns that are a part of the CurrInv.
i - the balance amt of each transaction will be written to Column F.

j - Once the cycle is complete for first SecID, we read the second SecID in the sorted column , and repeat step a to i
h - continue until all Rows have been processed

The XL file with required Output (sheet : AgeReport) is attached.

Can someone pls check and modify my code to obtain the required output.