FiFo Inventory

[vhanhto]

Hello,

I'm working on a problem and needing some help with a macro that will do FiFo. Example on 7/9/2013, the projected prod unit is 2241, what I need to do is applying the FiFo method by using the earliest inventory first so I will use 6/24, 6/25, 6/26, 6/27, 7/2, 7/3 and part of 07/8 inventory. So for the Oldest Cart date column, I need 7/9/13 row to show 07/08/13. How do I do this. I know very little about VBA and having a hard time following all the threads. Any help will be greatly appreciated.

Many Thanks,

Date	inventory	Projected Prod	Oldest Cart Date
6/24/13	100
6/25/13	200
6/26/13	300
6/27/13	290
6/28/13
6/29/13
6/30/13
7/1/13
7/2/13	400
7/3/13	625
7/4/13
7/5/13
7/6/13
7/7/13
7/8/13	2601
7/9/13	1763	2241
7/10/13	1763	2241
7/11/13	1763	2241
7/12/13	1763	2241
7/13/13	0	0
7/14/13	0	0
7/15/13	1802	2244
7/16/13	1801	2241
7/17/13	1801	2241
7/18/13	1801	2241
7/19/13	1801	2241

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[Warship]

Sub FiFo()
    Dim rgProjected As Range
    Dim rgInventory As Range
    Dim iInvenTotal As Long
    With Sheets("Sheet2") 'change Sheet name as needed
        Set rgProjected = .Cells(1, 3).End(xlDown)
        Set rgInventory = .Cells(2, 2)
    End With
    iInvenTotal = iInvenTotal + rgInventory
    Do Until iInvenTotal >= rgProjected
        Set rgInventory = rgInventory.Offset(1)
        iInvenTotal = iInvenTotal + rgInventory
    Loop
    rgProjected.Offset(, 1) = rgInventory.Offset(, -1)
End Sub

 

[vhanhto]

warship, Thank you so much for your code. It's working. How do I get it to go through the rest of the list? So on 07/10/13 (column A), we will use the left over inventory for 07/08/13 and then inventory for 07/09/13. So for that day the Oldest Cart date would be 07/09/13 and so forth down the list.

Many thanks for your help.