CODEWITHSUNDEEP
vbaexcelvlookup
Stupid_Intern
Stupid_Intern

Reputation: 3450

Unable to get worksheet the VLookup property of the WorksheetFunction Class error

enter image description here

Private Sub TextBox2_AfterUpdate()  'Badge Number
On Error GoTo Err
Dim tbl As ListObject, fndStr As String
Set tbl = Sheet9.ListObjects("EmployeeList")

fndStr = Trim(Me.TextBox2.Value)
MsgBox fndStr
If fndStr <> "" Then
    Me.TextBox3.Value = Application.WorksheetFunction.VLookup(fndStr, tbl, 2, False)    '<-- Error Line
End If

Exit Sub

Err:
MsgBox Err.Description

End Sub

I have a table named as "EmployeeList" and I am doing simple vlookup using Badge number but I am getting the error for unknown reason. I know there are similar questions asked before but I did read before posting this.

As you can clearly see the table name in the image and Entered value that is 10 for the first parameter on vlookup function but it doesn't returns any value but gives error. Don't know what's wrong.

  'I tried this as well
    Me.TextBox3.Value = Application.WorksheetFunction.VLookup(fndStr, Sheet9.Range("A1:F" & Rows.Count), 2, False)    '<-- Error Line

  'And this
   Me.TextBox3.Value = Application.WorksheetFunction.VLookup(fndStr, Sheet9.Range("EmployeeList"), 2, False)    '<-- Error Line

Also for unknown reason I can't do 

    Application.Vlookup as well

    Like when I do Application.V

    Vlookup doesn't shows up in the list.

Upvotes: 0

Views: 712

Answers (2)

vds1
vds1

Reputation: 75

Please find the code below, i have used evaluate method to get vlookup result.

Private Sub TextBox2_AfterUpdate()

Dim fndStr             As String
On Error GoTo Err_Desc

        fndStr = Trim(Me.TextBox2.Value)
        MsgBox fndStr
        If fndStr <> "" Then
            '// Using Eval method
            Me.TextBox3.Value = Evaluate("=VLOOKUP(" & fndStr & ",EmployeeList[#All],2,0)")
        End If
        Exit Sub

Err_Desc:
MsgBox Err.Description

End Sub

Upvotes: 1

Axel Richter
Axel Richter

Reputation: 61860

There are two issues.

The first, you have tried to solve, is that you need a Range as Arg2in Vlookup. Since your tbl is a ListObject. you could simply use tbl.Range, see ListObject.Range Property.

The second is, that Vlookup will not find strings in a column of numbers. And your first column is a column of numbers. So you need to convert the string into number.

Me.TextBox3.Value = Application.WorksheetFunction.VLookup(CDbl(fndStr), tbl.Range, 2, False)

should work.

Upvotes: 3

Related Questions