UDF help!!

[Young_Money]

Hi guys. I have a code that looks up the client initials and returns the cell to the right of it. However, I want this UDF to ONLY look in one sheet, "Reference_Location" for this information. However, I want the UDF to be able to be run in all worksheets in the excel file, ptwo.xlsm. How do I do this?
I know the Application.Workbooks part is incorrect, but I do not know how to correct it so that I can make it only use that ONE sheet.

Here is my code.

Function Custom_Lookup(ByVal Client_Initials As String) As String
Dim r
Application.Workbooks(“ptwo.xlsm").Worksheets(“Reference_Location”)
For Each r In Range("A:A")
If r = Client_Initials Then
Custom_Lookup = r.Offset(0, 1)
Exit Function
End If
Next

Custom_Lookup = "Not Found"

End Function

 

[AlphaFrog]

The quick answer to your question...

For Each r In Application.Workbooks("ptwo.xlsm").Worksheets("Reference_Location").Range("A:A")

You don't need to reference the workbook if the UDF is located within ptwo.xlsm

A couple of comments:

1. This UDF could be done with just a VLOOKUP formula or an INDEX-MATCH formula.
   
2. Also, if you really want to make the UDF, it would be much more efficient to use the .Find method instead of looping through each cell in column A. Column A has over a million cells to check.

 

[Young_Money]

Thanks alot AlphaFrog!