UDF - FIFO Valuation - Updating balance units as command progresses

[Lokesh]

I have a UDF which values the stock on FIFO basis and gives the total value at the end. I want the VB scrip to keep updating the balance units in respective rows while running. I am attaching the UDF for reference.

Appreciate if someone can help

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
End Function

 

[Lokesh]

Hey, anyone who can suggest me something on this...

 

[Norie]

You can't use a UDF to alter cell values, it will only return a value to the cell that it's in.

 

[Lokesh]

Thanks. Is there any other way I can update the cells - say by running a procedure or something