Vba

Copper Contributor

I have a vba file I am developing for sales transaction. If I run the vba file and I add a product, I get a run-time error 1004, that unable to get the vlookup property of the worksheetfunction class.

7 Replies
It's hard to say without seeing the vba code. As a shot in the dark, are you referencing the lookup range as "A1:B10", for example: application.WorksheetFunction.VLookup("x","A1:B10",2,0) which is one way it will fail with the error you are seeing), or are you using a range object, such as Range("A1:B10"), like: application.WorksheetFunction.VLookup("x",range("A1:B10"),2,0)
how do I send the file?
If you click on reply, instead of quick reply, you should see where you can attach a file - assuming there is no sensitive information. Otherwise, maybe copy/paste the applicable code. An incorrect argument being passed to the vlookup function may be the problem.

@JMB17 

The vlookup functions appear to be working okay for me. Is there a particular procedure and line that is problematic, or are there circumstances (userform inputs) where it's not working?

You could also be getting that error if the value being looked up doesn't exist in the table.

@JMB17 

I see. Your Add button (CommandButton6) is clearing the product and type comboboxes. When you clear those comboboxes, it triggers their respective change event handlers (cmb_Product_Change and cmb_Type_Change). Since the combobox values have been cleared, the vlookup formulas within those change event handlers fail.

You could add an IF statement to those event handlers to test the product combobox before attempting the vlookup.

Or, you could add a boolean module level variable to enable/disable events and add an if statement to your event handlers to check that variable and, if false, exit. That way, your code can set that variable to false when it's about to do something that will trigger another event handler that you don't want to run.