Excel

Function FIFO_PROFIT(SellPrice As Variant, SellQuantity As Variant, BuyPrice As Variant, BuyQuantity As Variant) As Variant
'Calculate the Profit according to the FIFO method


'---------------Check Information for errors----------------------
    SellSum = Application.WorksheetFunction.Sum(SellQuantity)
    BuySum = Application.WorksheetFunction.Sum(BuyQuantity)


    SellPCount = Application.WorksheetFunction.Count(SellPrice)
    SellQCount = Application.WorksheetFunction.Count(SellQuantity)
    BuyPCount = Application.WorksheetFunction.Count(BuyPrice)
    BuyQCount = Application.WorksheetFunction.Count(BuyQuantity)


    If SellSum > BuySum Then                                        'More sales than inventory, throw error
        FIFO_PROFIT = VBA.CVErr(XlCVError.xlErrValue)
    End If

    If (BuyPCount <> BuyQCount Or SellPCount <> SellQCount) Then    'Incomplete data, throw error
        FIFO_PROFIT = VBA.CVErr(XlCVError.xlErrValue)
    End If
'-----------------------------------------------------------------

'--------------MoreVariables--------------------------------------
Dim RunningSale As Variant
Dim RunningBuy As Variant
Dim RunningCost As Variant
Dim RunningBuyQuantity As Variant
Dim RunningSales As Variant
Dim RunningProfit As Variant
Dim Residual As Variant
Dim UsedupResidual As Variant
Dim y As Variant

y = 1
RunningCost = 0
Residual = 0
UsedupResidual = 0
RunningSales = 0
RunningProfit = 0
'-----------------------------------------------------------------


    For x = 1 To SellQCount

    If y <> 1 Then                                                                  'BUGtest

        RunningBuyQuantity = Residual + BuyQuantity(y).Value2
    End If
            While (RunningBuyQuantity <= SellQuantity(x).Value2 And y <= BuyQCount) 'Bugtest

                If y = 1 Then
                    RunningCost = RunningCost + (BuyPrice(y).Value2 * BuyQuantity(y).Value2)
                Else
                    RunningCost = RunningCost + ((BuyPrice(y).Value2 * BuyQuantity(y).Value2) + (BuyPrice(y - 1).Value2 * Residual))
                End If

                Residual = 0
                RunningBuyQuantity = RunningBuyQuantity + BuyQuantity(y).Value2
                y = y + 1

            Wend

        If RunningBuyQuantity > SellQuantity(x).Value2 Then
            Residual = SellQuantity(x).Value2 - RunningBuyQuantity
            UsedupResidual = BuyQuantity(y).Value2 - Residual
            RunningCost = RunningCost + (UsedupResidual * BuyPrice(y).Value2)
        End If

        RunningSales = SellPrice(x).Value2 * SellQuantity(x).Value2

        RunningProfit = RunningProfit + RunningSales - RunningCost

        RunningSales = 0
        RunningCost = 0

    Next x

    FIFO_PROFIT = RunningProfit



End Function