User Defined Function Call Error

[Paul_Hossler]

Maybe something like this in a standard module. No 'Private' on the Sub

Option Explicit
Public Function AreaOwner(BinName As String) As Variant
    Dim iBins As Long
    
    AreaOwner = CVErr(xlErrRef)
    
    With Worksheets("BinsAndOwners")
        For iBins = 2 To .Rows.Count
            With .Rows(iBins)
                If UCase(.Cells(1).Value) <= UCase(BinName) And UCase(BinName) <= UCase(.Cells(2).Value) Then
                    AreaOwner = .Cells(3).Value
                    Exit Function
                ElseIf Len(.Cells(1).Value) = 0 Then
                    Exit Function
                End If
            End With
        Next iBins
    End With
End Function

Sub AutomatedPickLog()
    Dim rBins As Range
    
    Set rBins = ActiveSheet.Range("D1")
    Set rBins = Range(rBins, rBins.End(xlDown))
    
    Range("E1").Formula = "=AreaOwner(D1)"
    Range("E1").AutoFill Destination:=rBins.Offset(, 1)
End Sub