VBA VLookup Error 1004

**Ronny66** · 05-17-2013, 07:10 AM

Hi All,

Im trying to create a userform in VBA in which I can manually enter a number in a textbox which then will search for the corresponding data in a different sheet.

Please Login or Register  to view this content.

When I try this, I get error 1004; "The properties of Vlookup of WoksheetFunction cannot be executed".

What am I doing wrong and how to fix this?

Thanks in advanced!!

**ARGK** · 05-17-2013, 07:31 AM

Hi
When it finds the corresponding data in a different worksheet what do you want to do and also which sheet do you want to search in?
Tony

**Ronny66** · 05-17-2013, 08:02 AM

When it finds the corresponding data; the exact same Number as I entered, I want it to show the corresponding costumer information such as name and adress.
This can be done by the last line in the code.

I want it to search in sheet4, as defined in the code.

Thanks for the reply!

**ARGK** · 05-17-2013, 08:13 AM

Hi Ronny
Looking at your code you are searching in columns A to Z but you are only returning the value in Column 2 of the search range if the value in VLOOKUP is found. On that basis you only need to search between columns A and B. This may be why you are getting the error.
Hope this helps.
Good luck.
Tony

**Norie** · 05-17-2013, 08:15 AM

Are the values in A2:A18 on Sheet4 numeric or text?

If they are numeric then you need to convert the text value from the textbox to numeric.

To do that you can try some of VBA's conversion functions like Val, CInt, CDbl etc

**Ronny66** · 05-17-2013, 08:37 AM

Originally Posted by Norie

Are the values in A2:A18 on Sheet4 numeric or text?

If they are numeric then you need to convert the text value from the textbox to numeric.

To do that you can try some of VBA's conversion functions like Val, CInt, CDbl etc

The values in A2:A18 are numeric and only numeric, just as the value I enter in the textbox. The corresponding data from B2:C18 is not numerical.

I've looked up the conversion functions but I can't really understand how to implement them in the code. Could you help me with that?

Hi Ronny
Looking at your code you are searching in columns A to Z but you are only returning the value in Column 2 of the search range if the value in VLOOKUP is found. On that basis you only need to search between columns A and B. This may be why you are getting the error.
Hope this helps.
Good luck.
Tony

Unfortunately this did not help

**Norie** · 05-17-2013, 09:40 AM

The values in the textbox are not numeric, they are text.

Give this a shot.

Please Login or Register  to view this content.

**Ronny66** · 05-17-2013, 10:10 AM

That does seem to work as long as the value I use in the sheet where it has to search from only has a single digit.

The values I need to use have 5 digits.

Thanks for the help!

**Norie** · 05-17-2013, 10:24 AM

If you are still having problem it might be an idea to attach a sample workbook.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**Ronny66** · 08-06-2013, 09:41 AM

Hi Guys,

A bit late, but better late than never.

I finally managed to fix the problem by adding a second step to the process; having a VLookUP in the worksheet do the work.
Lucky coincidence; this extra step adds more flexibility to the worksheet as several other worksheets can read from it too.

Thank you guys for your help!