User Defined Function Call Error

[dmelt253]

So this is related to an earlier post I submitted. Someone was very kind enough to give me a function that did exactly what I wanted. However the only way that I can call this function is by creating it as an add-in. I would much rather use this in a macro enabled workbook because then other people can utilize this Macro without installing the add-in.

I guess I just don't understand where to put the function or exactly how to call it in my sub. Here is an excerpt of the code I have since the whole Macro is Very long.

Option Explicit

Public Function AreaOwner(BinName As String) As Variant
    Dim iBins As Long
     
    AreaOwner = CVErr(xlErrRef)
     
    With Worksheets("AreaOwnerKey")
        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

Private Sub AutomatedPickLog()
Range("E1").Select
    ActiveCell.FormulaR1C1 = "=AreaOwner(RC[-1])"
    Selection.AutoFill Destination:=Range(Range("E1"), Range("F1").End(xlDown).Offset(0, -1)), Type:=xlFillDefault
End Sub

Basically I am trying to use the Function like a formula that evaluates D1 and puts the answer E1 and then repeats this step for all other cells in column D. However when I run this Macro the function results in a ?NAME error.

Is there a better way to do this?

 

[Sektor]

You can put this code into any module of workbook - it's not tied to add-in.

 

[dmelt253]

You can put this code into any module of workbook - it's not tied to add-in.

So why would this function work as an add-in but not as a function in a module? Is it because I'm trying to call it as a formula? I tried calling just the value

Cells(1, 5).Value = AreaOwner(Cells(1, 4))

And now I am getting a Type mismatch (error 13).

 

[Richard U]

you're passing the function a range when it's expecting a string.

try this instead

Cells(1, 5).Value = AreaOwner(Cells(1, 4).value)