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

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

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  lt  gt     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  lt  gt  Result Then         MsgBox cell Value     End If      On Error GoTo 0

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