19

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?

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
user2864307
  • 191
  • 1
  • 1
  • 3

3 Answers3

15

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
Santosh
  • 12,175
  • 4
  • 41
  • 72
7

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(<Value>).

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.

Omar Khan
  • 81
  • 1
  • 3
3

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.

mjpowers0903
  • 71
  • 2
  • 7