Hello All - I am attempting to write a formula that will capture the inventory value on hand for various products. I have entered all the data in the spreadsheet and found some code online that I think will assist in the calculation. The problem is I don't know how to adapt what I found in VBA to the spreadsheet I have already started. Anyone who could help me understand what needs to be done?
In the excel file, Inventory Calc tab, cell F9 is where I would want the spreadsheet to calculate what the price per unit of inventory should be. The information will come from the Order Entry tab. Let me know what additional information will be useful.
Project Help.xlsx
Function FIFO(ProductCode As Range, UnitsSold As Range) As Currency
Dim StartCount As Range, UnitCost As Range, Products As Range, PurchaseUnits As Range
Dim Counter As Integer, RemainingUnits As Long, UnitsAccountedFor As Long
FIFO = 0
Set Products = Range("ProductCode")
Set StartCount = Range("StartCount")
Set UnitCost = Range("UnitCost")
Set PurchaseUnits = Range("PurchaseUnits")
UnitsAccountedFor = UnitsSold
For Counter = 1 To StartCount.Rows.Count
If ProductCode = Products(Counter, 1) Then
RemainingUnits = Application.WorksheetFunction.Max(0, StartCount(Counter, 1) + _
PurchaseUnits(Counter, 1) - UnitsAccountedFor)
FIFO = FIFO + UnitCost(Counter, 1) * RemainingUnits
UnitsAccountedFor = UnitsAccountedFor - (StartCount(Counter, 1) + _
PurchaseUnits(Counter, 1) - RemainingUnits)
End If
Next Counter
Bookmarks