I tried to write the below code to call a new created function [Function FIFO(ProductCode As Range, UnitsSold As Range) As Currency], and to be applied for each row of Column "L" in sheet2 but it does not apply would you mind correcting it, please?
Sub EndingInventory() Dim cell As Range, r1 As Range, r As Range Dim rem As Variant Dim FIFO1 As Long Set r = Sheet2.Range("L2:L12") For Each cell In r
Call FIFO(Cells(cell.Row, "H").Text, Cells(cell.Row, "I").Value) rem = FIFO If Not IsError(res) Then If IsNumeric(res) Then rem = rem cell.Value = rem Else cell = rem End If Else cell.ClearContents End If
Next End Sub
Many Thanks Lurenzo
' Dummy FIFO Function to demonstrate assigning a value to the function name ' the function should return a Text Value, a Numeric Value or an Error. Text ' or number is decided by the values in columns H and I in rows 2 to 12.
Public Function FIFO(ProductCode As Range, UnitsSold As Range) Dim d As Single, ans As Variant d = Rnd() If d < 0.3 Then
ans = ProductCode.Value
ElseIf d < 0.6 Then
ans = UnitsSold.Value
ans = CVErr(xlErrNA)
End If FIFO = ans End Function
Sub EndingInventory() Dim cell As Range, r As Range Dim res As Variant ' rem is a VBA keyfor Remark. You can't us it as a variable
' I have changed it to "res"
' if you qualify Range with Sheet2, then you should probably also qualify ' Cells in the arguments to FIFO Set r = Sheet2.Range("L2:L12") For Each cell In r
' your function declaration is expecting range references as arguments res = FIFO(Sheet2.Cells(cell.Row, "H"), Sheet2.Cells(cell.Row, "I"))
If Not IsError(res) Then
If IsNumeric(res) Then cell.Value = res Else ' I just added the string "Product Code: " to make the output look different ' when I tested the function. You can me this line whatever it should be. cell.Value = "Product Code: " & res End If
End If Next End Sub
These were tested and worked as I expected. Hope that helps.