VBA Vlookup not returning values

[richardlewis07]

Hi all,

I have nto written macros for some years and so am having to grasp things again. I have a vlookup which searches for a number obtained from a text file input using mid and search functions. It looks up in a range within another sheet. these are numbers.

I keep getting error 2042 when i have tested values that are true and false. I have also converted the data type of the string look up to be Lng nut still it fails.

I have also tried to use worksheetfunction but it did not work. I have also tried using .value on the end.

The vba is below.

Any help would be greatly appreciated.

Thanks,
Rich

Please Login or Register  to view this content.

[zbor]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[richardlewis07]

Hi Zbor, apologies for that. Here you go.

Please Login or Register  to view this content.

[zbor]

it's enough to edit your original post (I will do that now for you).

[OllieB]

Hi Richard,

setting the Nrange range could be written as

Please Login or Register  to view this content.

the vlookup statement contains a typing error, but could also be re-written as

Please Login or Register  to view this content.

or alternatively (no need for Nrange)

Please Login or Register  to view this content.

[OllieB]

Hi Richard,

setting the Nrange range could be written as

Please Login or Register  to view this content.

the vlookup statement contains a typing error, but could also be re-written as

Please Login or Register  to view this content.

or alternatively (no need for Nrange)

Please Login or Register  to view this content.

[richardlewis07]

Many thanks for your response Ollie B.
* I have tried the solution but it still doesn't find the value.*
Shall I upload the data sheets too?**

Many thanks,
Rich

[OllieB]

Sure, why not. I will have a look for you

[richardlewis07]

Many thans for that Ollie. Sorry for the delay in responding, I have some problems responding when I am in work; for some reason the page eeps timing out.

I have attached the excel.

Many thanks in adavnce,
Rich

[OllieB]

Richard,

With all respect, the code could do with some cleaning up, but then again that is just my opinion

To get it all to work:
- remove the Match instruction using the Nrange object (delete the statement),
- and change the Match instruction using the column(2) to:

Please Login or Register  to view this content.

i.e. I added the Val(...) instruction to make sure the numeric value is used for the search

[richardlewis07]

Ollie B,
Many thanks , It is much appreciated. Yes I will tidy it up. I've not used vba for about5 years and hence it is clunky. Any pointers are greatly received.

Rich

[OllieB]

Richard,

I think the code below does pretty much the same as your code. Just for fun

Please Login or Register  to view this content.