Option Explicit
Function FIFO(ByVal Product As String, _
ByVal Units As Long, _
ByVal DataRange As Range, _
Optional Delimiter As String = ";") As String
Dim iCol As Integer
Dim lRowFr As Long, lRowTo As Long
Dim lRow As Long, lRowEnd As Long, lBought As Long
Dim sReply As String, sCur As String
Dim dCost As Double, dCostTot As Double
Dim WS As Worksheet
sReply = "FIFO(" & Product & "," & Units & "," & DataRange.Address(False, False) & ")"
Set WS = Sheets(DataRange.Parent.Name)
lRowFr = DataRange.Row
lRowTo = DataRange.Rows.Count + lRowFr - 1
iCol = DataRange.Column
For lRow = lRowFr To lRowTo
sCur = WS.Cells(lRow, iCol).Text
If sCur = Product Then
lBought = Val(WS.Cells(lRow, iCol + 1).Value)
Units = Units - lBought
If Units < 0 Then lBought = lBought + Units
dCost = Val(WS.Cells(lRow, iCol + 2).Value)
dCostTot = dCostTot + (lBought * dCost)
sReply = sReply & Delimiter & lBought & " @ £" & dCost
If Units <= 0 Then Exit For
End If
Next lRow
sReply = sReply & Delimiter & "Total=£" & dCostTot
FIFO = sReply
End Function
Function LIFO(ByVal Product As String, _
ByVal Units As Long, _
ByVal DataRange As Range, _
Optional Delimiter As String = ";") As String
Dim iCol As Integer
Dim lRowFr As Long, lRowTo As Long
Dim lRow As Long, lRowEnd As Long, lBought As Long
Dim sReply As String, sCur As String
Dim dCost As Double, dCostTot As Double
Dim WS As Worksheet
sReply = "LIFO(" & Product & "," & Units & "," & DataRange.Address(False, False) & ")"
Set WS = Sheets(DataRange.Parent.Name)
lRowFr = DataRange.Row
lRowTo = DataRange.Rows.Count + lRowFr - 1
iCol = DataRange.Column
For lRow = lRowTo To lRowFr Step -1
sCur = WS.Cells(lRow, iCol).Text
If sCur = Product Then
lBought = Val(WS.Cells(lRow, iCol + 1).Value)
Units = Units - lBought
If Units < 0 Then lBought = lBought + Units
dCost = Val(WS.Cells(lRow, iCol + 2).Value)
dCostTot = dCostTot + (lBought * dCost)
sReply = sReply & Delimiter & lBought & " @ £" & dCost
If Units <= 0 Then Exit For
End If
Next lRow
sReply = sReply & Delimiter & "Total=£" & dCostTot
LIFO = sReply
End Function