Calculate the cost by FIFO with VBA.

[Anbuselvam K]

Dear VBA Developer,

The attached Excel file contains two sheets: Master Data and RM Purchase.

In the Master Data sheet, Column B lists the Project Locations, with consumption data spanning from columns 3 to 23.

In the RM Purchase sheet, Column 3 also features Project Locations, with price information from columns 4 to 24 and Purchase Quantities from columns 25 to 45.

The code below calculates the cost of RM01 in Column 24 of the Master Data sheet. I would like to apply the same calculation method to determine the costs for RM02 through RM21 within the below code modifications. Expecting your support.

Thank you to @Marc L for providing the foundational code for this requirement in my previous thread.

Sub Costs_of_RM01()
    Dim D() As Variant, B, CP, PP, PQ, R&, L&, M@

    With Sheets("Master Data")
        Dim LastRow As Long
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        B = .Range("B8:B" & LastRow).Resize(, 2).Value
        CP = Sheets("RM Purchase").Range("C8:C" & LastRow).Value
        PP = Sheets("RM Purchase").Range("D8:D" & LastRow).Value
        PQ = Sheets("RM Purchase").Range("Y8:Y" & LastRow).Value
        ReDim D(1 To LastRow - 7, 0)

        For R = 8 To LastRow
            Dim CompanyName As String
            CompanyName = UCase(Trim(B(R - 7, 1)))
            L = 0

            For i = 1 To UBound(CP, 1)
                If UCase(Trim(CP(i, 1))) = CompanyName Then
                    L = i
                    Exit For
                End If
            Next i

            If L > 0 Then
                Dim ConsumptionQuantity As Double
                ConsumptionQuantity = .Cells(R, "C").Value
                If IsNumeric(ConsumptionQuantity) And ConsumptionQuantity > 0 Then
                    Dim RemainingConsumption As Double
                    RemainingConsumption = ConsumptionQuantity
                    Dim TotalCost As Double
                    TotalCost = 0

                    Do While RemainingConsumption > 0 And L <= UBound(CP, 1)
                        Dim PurchaseQty As Double
                        PurchaseQty = PQ(L, 1)
                        If PurchaseQty > 0 Then
                            Dim ConsumedQty As Double
                            If RemainingConsumption >= PurchaseQty Then
                                ConsumedQty = PurchaseQty
                            Else
                                ConsumedQty = RemainingConsumption
                            End If
                            TotalCost = TotalCost + (ConsumedQty * PP(L, 1))
                            RemainingConsumption = RemainingConsumption - ConsumedQty
                            PQ(L, 1) = PurchaseQty - ConsumedQty
                        End If
                        L = L + 1
                    Loop
                    D(R - 7, 0) = TotalCost
                Else
                    D(R - 7, 0) = 0
                End If
            Else
                D(R - 7, 0) = 0
            End If
NextR:
        Next R

        .Range("X8:X" & LastRow) = D
    End With
End Sub