Unable to get the VLookup property of the WorksheetFunction Class error

Question

I am trying to develop a form to track invoices as they come in  The form will have a combobox where I can click on and select a vendor number  I want the textbox to automatically fill in based on the vendor number selected from the combobox   Here s what I have so far   Private Sub ComboBox1 Change            Vlookup when ComboBox1 is filled     Me TextBox1 Value   Application WorksheetFunction VLookup            Me ComboBox1 Value  Worksheets  Sheet3   Range  Names    2  False      End Sub   Worksheet 3 is from which the information is being drawn  the vendor number and name      When I go back to the form to test the code  I get the following error      Run-time error  1004   Unable to get the VLookup property of the WorksheetFunction class   How do I fix this

User · Answer

Try below code

I will recommend to use error handler while using vlookup because error might occur when the lookup_value is not found.

Private Sub ComboBox1_Change()


    On Error Resume Next
    Ret = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, Worksheets("Sheet3").Range("Names"), 2, False)
    On Error GoTo 0

    If Ret <> "" Then MsgBox Ret


End Sub

OR

 On Error Resume Next

    Result = Application.VLookup(Me.ComboBox1.Value, Worksheets("Sheet3").Range("Names"), 2, False)

    If Result = "Error 2042" Then
        'nothing found
    ElseIf cell <> Result Then
        MsgBox cell.Value
    End If

    On Error GoTo 0

User · Answer

I was having the same problem  It seems that passing Me ComboBox1 Value as an argument for the Vlookup function  is causing the issue  What I did was assign this value to a double and then put it into the Vlookup function   Dim x As Double x   Me ComboBox1 Value Me TextBox1 Value   Application WorksheetFunction VLookup x  Worksheets  Sheet3   Range  Names    2  False     Or  for a shorter method  you can just convert the type within the Vlookup function using Cdbl  lt Value gt     So it would end up being  Me TextBox1 Value   Application WorksheetFunction VLookup Cdbl Me ComboBox1 Value   Worksheets  Sheet3   Range  Names    2  False     Strange as it may sound  it works for me   Hope this helps

User · Answer

I was just having this issue with my own program   I turned out that the value I was searching for was not in my reference table   I fixed my reference table  and then the error went away

[vba] "Unable to get the VLookup property of the WorksheetFunction Class" error

Examples related to vba

Examples related to excel

Examples related to vlookup