Run-time error 1004 : Unable to get Vlookup property of the WorksheetFunction class

[manthony]

Hi,
I am getting a Run-time error 1004 : Unable to get Vlookup property of the WorksheetFunction class.

I am trying to use a vlookup to find an employee’s name from their employee number and put that into a welcome statement message box.

The “Me.txtUser.Value “ in code below is where the error occurs. If I hard code an employee number, the code works fine.

The “Me.txtUser.Value” is coming from the employee number entered into a userform when the workbook is opened.

Faulty Code:
MyVal = Application.WorksheetFunction.VLookup(Me.txtUser.Value, Sheet2.Range("V8:W1000"), 2, False)

Also on a side note, Dim for MyVal is set to:
Dim MyVal As String

and

Me.txtUser.Value variable is equal to user.
user = Me.txtUser.Value

Thank you in advance for your help.

 

[Michael M]

I can't test this but try

=VLOOKUP( & Me.txtUser.Value & ,Sheet2!V8:W1000, 2, FALSE)

 

[Michael M]

Also try...
and check to see that TU actually holds the txtbox value

TU = Me.txtuser.Value
myval = Application.WorksheetFunction.VLookup(" & TU & ", Sheet2! & Range("V8:W1000"), 2, False)

 

[manthony]

Also try...
and check to see that TU actually holds the txtbox value

TU = Me.txtuser.Value
myval = Application.WorksheetFunction.VLookup(" & TU & ", Sheet2! & Range("V8:W1000"), 2, False)

Hi Michael,

Thank you so much for the help. It put me on the right track. I had Me.txtuser.Value= user, so I used that instead of TU and tinkered with the code a bit more to get it to work.

My final code was "MyVal = Application.VLookup(Val(user), Sheet2.Range("V8:W1000"), 2, False)"

Now my issue is if someone entered an invalid employee number I get a Run-time error 13: Type Mismatch on the same line. I do not know if you have a good error handling tip so that if the vlookup returns an invalid result it can popup a new message box saying "invalid employee number"

Thanks again for all your help, I greatly appreciate it.

 

[George J]

Not sure about this, but can you put
MyVal = application.worksheetfunction.iferror(#what you have#,#value to return on error#)

 

[manthony]

That worked like a charm. Thank you so much for the help.