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,

[TABLE="width: 340"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]inventory[/TD]
[TD]Projected Prod[/TD]
[TD]Oldest Cart Date[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6/24/13[/TD]
[TD="align: right"]100[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6/25/13[/TD]
[TD="align: right"]200[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6/26/13[/TD]
[TD="align: right"]300[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6/27/13[/TD]
[TD="align: right"]290[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6/28/13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6/29/13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6/30/13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/1/13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/2/13[/TD]
[TD="align: right"]400[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/3/13[/TD]
[TD="align: right"]625[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/4/13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/5/13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/6/13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/7/13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/8/13[/TD]
[TD="align: right"]2601[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/9/13[/TD]
[TD="align: right"]1763[/TD]
[TD="align: right"]2241[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/10/13[/TD]
[TD="align: right"]1763[/TD]
[TD="align: right"]2241[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/11/13[/TD]
[TD="align: right"]1763[/TD]
[TD="align: right"]2241[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/12/13[/TD]
[TD="align: right"]1763[/TD]
[TD="align: right"]2241[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/13/13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/14/13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/15/13[/TD]
[TD="align: right"]1802[/TD]
[TD="align: right"]2244[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/16/13[/TD]
[TD="align: right"]1801[/TD]
[TD="align: right"]2241[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/17/13[/TD]
[TD="align: right"]1801[/TD]
[TD="align: right"]2241[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/18/13[/TD]
[TD="align: right"]1801[/TD]
[TD="align: right"]2241[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/19/13[/TD]
[TD="align: right"]1801[/TD]
[TD="align: right"]2241[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[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.