VBA Xlookup Never Returns Results

[Rex2024]

I would like my user to enter their employee number in a field which will then lookup their name upon clicking a command button. I will transfer this name to my next form for various uses.

Employee Numbers are in column A & First name in column B

This formula works in the worksheet with the data and want to just enter an employee number in cell F2:

=XLOOKUP(F2, A:A, B:B, "Not Found", 0, 1)

But this doesn't work in the VBA form, it always returns "Not found":

Sub OpenRecordLookups_Click()

Dim DPUsingForm As String

DPUsingForm = Application.WorksheetFunction.XLookup(Me.DPEmpNum.Value, ThisWorkbook.Sheets("AuthorizedUsers").Range("A:A"), ThisWorkbook.Sheets("AuthorizedUsers").Range("B:B"), "Not Found", 0, 1)

MsgBox "Welcome employee " & DPUsingForm

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

With ThisWorkbook.Sheets("AuthorizedUsers")
DPUsingForm = Application.XLookup(CLng(Me.DPEmpNum.Value), .Range("A:A"), .Range("B:B"), "Not Found", 0, 1)
End With

 

[Rex2024]

That worked!

Hi & welcome to MrExcel.
How about

With ThisWorkbook.Sheets("AuthorizedUsers")
DPUsingForm = Application.XLookup(CLng(Me.DPEmpNum.Value), .Range("A:A"), .Range("B:B"), "Not Found", 0, 1)
End With

That worked!

Why did that work and what I did not work?

 

[Fluff]

Assuming that DPEmpNum is a textbox, combobox or similar it will always return text & not a number, wrapping it in CLng converts it to a number.

 

[Rex2024]

Fascinating. Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.