How can I change this to a certain range rather than entire column?

[VBE313]

How can I refer to a range rather than entire column?

Thanks


Function Level5MakeMaterialCost()
Dim R As Long
Application.Volatile (True)
R = Application.Caller.Row + 1
Do While Cells(R, "BN").Value <> "Yes"
If Cells(R, "BM") = "" Then Exit Do
Level5MakeMaterialCost = Level5MakeMaterialCost + Val(Cells(R, "BM").Value)
R = R + 1
Loop
End Function

 

[dmt32]

Hi,
on idea maybe would be to add a range parameter to your function & pass the required range to it

Function Level5MakeMaterialCost(ByVal Target As Range)
    Dim cell As Range
    
    Application.Volatile
    
    For Each cell In Target.Cells
      If UCase(cell.Offset(, 1).Value) = "YES" Or cell.Value = "" Then Exit For
        Level5MakeMaterialCost = Level5MakeMaterialCost + Val(cell.Value)
    Next cell
End Function

=Level5MakeMaterialCost(BM4:BM8)

Dave