UDF returning #Value when another file is opened

[Ray Bailey]

Hi,

I've cut this down as much as I can but the full file has 28 columns and 2000+ rows.

I'm trying to insert functions into a worksheet so that the users can find data based the value in column A. Effectively doing a vlookup via a function. Reason for this is that user are not too handy with vlookups etc, each user will want data in different columns depending on their own requirements & I don't want them to have direct access to the master file.

Tab SLA_Calc simply has "12345" in A1 and "Active" in B1

Tab Tracker has "12345" in A1 and "=zStatus(A1)" in B1 ... zStatus is the function below and returns "Active"

The function works find (in sample and full file) until I open another workbook and the zStatus function return #Value

Where am I going wrong???

Function zStatus(iRec)


    Application.Volatile


    Dim rng As Range
    Dim col As Integer
    Dim found As Variant


    Set rng = Sheets("SLA_Calc").Columns("A:B")


    On Error Resume Next
    found = Application.VLookup(iRec, rng, 2, 0)
    If IsError(found) Then
        zStatus = "Not Found"
    Else
        zStatus = found
    End If
    On Error GoTo 0


End Function

 

[JoeMo]

Does the other workbook contain a sheet named "SLA_Calc"?

 

[Ray Bailey]

Hi Joe,

No, there's no duplication with sheet or function names.

I've just done File > New then saved as Book1, so nothing entered. Closed and re-opened, Still get the same issue.


Cheers

Ray