Macro Vlookup does not recognizes named range

[GIS2013]

Hi, the code below returns error 1004. And when I replace "myRange" at the vlookup line with "a1:g4" it works. What's wrong with my code?

Please Login or Register  to view this content.

[Solus Rankin]

In that case it would be a declared variable, not a named range. Try:

Please Login or Register  to view this content.

[GIS2013]

Hi, No I get this error again..I add now the full code, and hope that till tommorrow something will pop up. thanks

Please Login or Register  to view this content.

[Solus Rankin]

Where are you getting the error and what kind? You have undeclared variables.

Also I'm not that strong with Vlook ups but doesn't the first part need to be a value? So:

Please Login or Register  to view this content.

[Norie]

You don't need Range.

Please Login or Register  to view this content.

By the way, what are you actually looking up?

The lookup range you is 7 columns wide but you are only returning the first (A) column.

[watersev]

hi GIS2013, you are using 1 column for the result to come from Vlookup. That would give you an error. Change the column number in Vlookup or use different set of functions (Match, INDEX) if you want to return value on the left from the searched column

[Roger Govier]

Hi

Without seeing your data it is difficult to be sure, but
Sheets("1").Activate is not the same sheet you refer to for lr = Sheets(1).UsedRange.Rows.Count

and

Result = Application.VLookup(Range("J4"), Range(myRange), 1, False)

should be

Result = Application.VLookup(Range("J4"), myRange, 1, False)

[GIS2013]

Thank you all. @norie - to your question, trying to build a search form. But it's more complicated than I though. I was thinking that a user would type "Bob", for example, in a textbox in the userform, and then click a button that runs vlookup search throughout the db and gets back the rest of the data regarding that name (if item = 1 to 7, e.g. 7 items per person, and the user looks for the i item, he'll get back the other 7-i items..)

[Norie]

You don't need Vlookup for that, use Match instead.

Match will return the row number that the user input, eg Bob, is entered and from that you can return the rest of the data for 'Bob', or whoever.

Let's say TextBox1 is where the user enters the name and the data is returned to TextBox2-9.

Please Login or Register  to view this content.

[GIS2013]

@norie, just a question about the "me" you put twice along the code. What does it do? the code is not running because of this..

[Norie]

Me refers to the userform the code is in.

It shouldn't stop the code running, unless of course the code isn't in the userform module.

Perhaps you could upload a sample workbook?

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

[GIS2013]

It really wasn't but now it is. And now no error, but I still dno't get results. Very weird the code looks perfect. Note I've changed from textbox1 to textbox15 (but same idea - the box the user enters what he's looking for), and the For loop runs from textboxed 16 to 17, but also the same idea as you meant..

[GIS2013]

@norie, I've just finished making a sample file, then I run it (the sample file) and wonder of wonders it's working. Now it's my job to figuring out what fails my real db. Really appreciate your help