Code:
Function previous(ByVal status_x As Range, ByVal date_x As Range, ByVal bin_x As Range) As String
For Each C In Sheet6.Range(Range("F9"), Range("F9").End(xlDown)).Cells
If Month(C.Value) = Month(date_x.Value) And Day(C.Value) < Day(date_x.Value) And Cells(C.Row, bin_x.Column).Text = bin_x.Text Then
If Cells(C.Row, status_x.Column).Text = "NFI" Or Cells(C.Row, status_x.Column).Text = "Hang" Or Cells(C.Row, status_x.Column).Text = "Empty" Then
Update = Cells(C.Row, status_x.Column).Value
Exit Function
End If
End If
Next
previous = status_x.Value
End Function
I've created this custom function to look up a previous status of bins entered earlier in the sheet if the current conditions are "Empty", "Hang", or "NFI".
It seems to work fine with hangs and empties, but with NFIs it spits back #NAME? errors. Not sure why.
Any idea - am I messing up on the syntax somewhere?