VBA vlookup won't find numbers - runtime error 1004

**wonderdunder** · 05-26-2011, 09:30 AM

I've got a simple VBA code for a userform.

Textbox 1 is a list box that allows users to pick from a list (Sheet 1, row A, which are numbers automatically entered by another userform). I'm using vlookup to populate other text boxes on the form (this is in the change property for textbox 1):

Please Login or Register  to view this content.

The problem is that I get a runtime error 1004 "unable to get the Vlookup property of the worksheetfunction class".

However, if I change the values in A (for example, instead of 4 I typed A4), then it will see it and populate other text boxes accordingly. I've tried typing in "4" in the cell in the same manner but I still get the runtime error. I've tried changing the formatting of cells in row A but that doesn't work either.

Can someone please save me from a nervous breakdown?!

**wonderdunder** · 05-26-2011, 03:34 PM

OK, let me put this out.

Row A is an automatically generated number using VBA - simply looking at the value of the last row then adding a new row with the cell value increased by 1. So A2 maybe 20, then A3 will be 21.

My vlookup wont find this, but if I changed A3 to anything containing text, then it will work on that value. I've no idea why.

So I'm thinking that if I simply change the VBA to include a letter before the number, then this vlookup will work. But how do I do that? Curently it just looks at the last value and adds 1 to it. That obviously won't happen if a letter preceeds the number. I know that you can use LEFT or RIGHT in a spreadsheet but how would I work this into VBA?

**shg** · 05-26-2011, 03:45 PM

Try

Please Login or Register  to view this content.

**wonderdunder** · 05-26-2011, 03:54 PM

Thanks, but I just get the same result. When I try it with the cell that I put text in, I get a type mismatch error, but I suppose that's because it's looking at a number and not a string?

Thing is, I've got another piece of code that pick up the number no problems. I've tried copying it but I get no joy. This is really doing my head in!

**shg** · 05-26-2011, 04:08 PM

The textbox contains a numeric values, and you are searching for that value in a list of numbers (not text that looks like numbers?)

**wonderdunder** · 05-26-2011, 04:09 PM

To add, I tried using a conditional loop so that if the value in the text box matched a value in column A, then it would populate another textbox using an offset value.

It didn't work as the textbox was not populated with any data, but it should have been. Does that suggest anything (besides me being incompetent?!)

**wonderdunder** · 05-26-2011, 04:16 PM

Originally Posted by shg

The textbox contains a numeric values, and you are searching for that value in a list of numbers (not text that looks like numbers?)

The textbox uses a rowsource - essentially a list of column A. Column A is formatted as numeric with 0 d.p.

This textbox displays the values in column A - there are no problems there. But when I use the Vlookup function I just get that 1004 error.

I changed Cell A3 from "3" to "J3" and again, it appeared in the list but the vlookup worked. It did not work for anything else.

If it helps, here's the code that I'm using in the userform to enter data in the first place:

Please Login or Register  to view this content.

The code I'm using to find and display this information is as I've posted in original thread.
Can you see anything wrong?

**wonderdunder** · 05-26-2011, 04:40 PM

Originally Posted by shg

Try

Please Login or Register  to view this content.

I tried looking at what the CDbl thingy does, and came across a thread that suggested using val, though it was in an entirely different contact. I changed the CDbl part to Val (out of desperation) and it works!!!!! To paraphrase Orwell, I understand how, but now why. Any ideas? I'm becoming a bit more adept at this lark (thanks to this forum), but I'm nowhere near any level of competence.

**shg** · 05-26-2011, 04:49 PM

I'm surprised. Val and CDbl do the same thing, except that CDbl is international-aware and Val isn't.

Anyway, glad you got it sorted.

**wonderdunder** · 05-26-2011, 04:53 PM

Maybe it's just my version of Excel. In any case, I don't care! - I've spent 4 days trying to solve this and still be none the wiser where it not for you many thanks!