I have the following worksheet which works pretty well with the VBA code, except that when I put decimals in column A it throws off the FIFO inventory for some reason. Unfortunately we need to use decimals in column a. I have tried creating helper columns that convert the tons to pounds, but it causes the spreadsheet to lag something fierce. Any ideas on why the decimals cause this to fail? I have pasted the vba code I am using under the worksheet below.
Thanks!
[TABLE="width: 500"]
<tbody>[TR]
[TD]purchased tons[/TD]
[TD]price/ton[/TD]
[TD]tons used[/TD]
[TD]FIFO Ending Value[/TD]
[/TR]
[TR]
[TD]19375[/TD]
[TD]7.8565[/TD]
[TD]3375[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14211[/TD]
[TD].3518[/TD]
[TD]3234[/TD]
[TD]105296.32[/TD]
[/TR]
[TR]
[TD]15605[/TD]
[TD].6408[/TD]
[TD]42572[/TD]
[TD]6.41[/TD]
[/TR]
[TR]
[TD]120000[/TD]
[TD]2.9167[/TD]
[TD]11910[/TD]
[TD]291.67[/TD]
[/TR]
</tbody>[/TABLE]
Option Explicit
Function FIFO(PurchaseUnits As Range, UnitCost As Range, UnitsSold As Range, Optional blnAscending As Boolean = False) As Double
Dim Counter As Long, UnitsAccountedFor As Long
Dim varPurchased, varCost
FIFO = 0
UnitsAccountedFor = Application.Sum(UnitsSold)
varPurchased = PurchaseUnits.Value
varCost = UnitCost.Value
If blnAscending Then
Counter = LBound(varPurchased, 1)
Do Until UnitsAccountedFor = 0
If varPurchased(Counter, 1) = 0 Then
Counter = Counter + 1
Else
varPurchased(Counter, 1) = varPurchased(Counter, 1) - 1
UnitsAccountedFor = UnitsAccountedFor - 1
End If
Loop
Else
Counter = UBound(varPurchased, 1)
Do Until UnitsAccountedFor = 0
If varPurchased(Counter, 1) = 0 Then
Counter = Counter - 1
Else
varPurchased(Counter, 1) = varPurchased(Counter, 1) - 1
UnitsAccountedFor = UnitsAccountedFor - 1
End If
Loop
End If
FIFO = Application.SumProduct(varPurchased, varCost)
End Function
Thanks!
[TABLE="width: 500"]
<tbody>[TR]
[TD]purchased tons[/TD]
[TD]price/ton[/TD]
[TD]tons used[/TD]
[TD]FIFO Ending Value[/TD]
[/TR]
[TR]
[TD]19375[/TD]
[TD]7.8565[/TD]
[TD]3375[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14211[/TD]
[TD].3518[/TD]
[TD]3234[/TD]
[TD]105296.32[/TD]
[/TR]
[TR]
[TD]15605[/TD]
[TD].6408[/TD]
[TD]42572[/TD]
[TD]6.41[/TD]
[/TR]
[TR]
[TD]120000[/TD]
[TD]2.9167[/TD]
[TD]11910[/TD]
[TD]291.67[/TD]
[/TR]
</tbody>[/TABLE]
Option Explicit
Function FIFO(PurchaseUnits As Range, UnitCost As Range, UnitsSold As Range, Optional blnAscending As Boolean = False) As Double
Dim Counter As Long, UnitsAccountedFor As Long
Dim varPurchased, varCost
FIFO = 0
UnitsAccountedFor = Application.Sum(UnitsSold)
varPurchased = PurchaseUnits.Value
varCost = UnitCost.Value
If blnAscending Then
Counter = LBound(varPurchased, 1)
Do Until UnitsAccountedFor = 0
If varPurchased(Counter, 1) = 0 Then
Counter = Counter + 1
Else
varPurchased(Counter, 1) = varPurchased(Counter, 1) - 1
UnitsAccountedFor = UnitsAccountedFor - 1
End If
Loop
Else
Counter = UBound(varPurchased, 1)
Do Until UnitsAccountedFor = 0
If varPurchased(Counter, 1) = 0 Then
Counter = Counter - 1
Else
varPurchased(Counter, 1) = varPurchased(Counter, 1) - 1
UnitsAccountedFor = UnitsAccountedFor - 1
End If
Loop
End If
FIFO = Application.SumProduct(varPurchased, varCost)
End Function