error on vlookup

Hi

I tried to standardize the supplier name by using vlookup function.

If the Supplier exist on the vlookup range, it will change the company name to its full name. And, at the same time the Activities of that company will automatically added.

This is my code:

Please Login or Register  to view this content.

I hope anyone can help me solving this problem.
Thanks in advance!

The WorksheetFunction.VLookup will throw a Debug if no Match is found.

Rather than add a suitable Error Handler (vba, function) it is generally simpler to revert to Application.VLookup (which will not debug but will return Error #) and validate the result to see if an Error was returned.

Please Login or Register  to view this content.

That said I don't understand your use of Value given your sample file ... ie the code will never find a match in present form.

A point I think I made in the prior post, no ?

Originally Posted by D.O

That said I don't understand your use of Value given your sample file ... ie the code will never find a match in present form.

If you're not sure why something is not working - always step through the code (F8) and monitor via Locals Window etc...

In your code you specify:

Please Login or Register  to view this content.

Based on your sample data LookupVal is always going to be 0
Given 0 does not exist in your table_array the VLOOKUP is always going to fail.

Originally Posted by isabella

Is that because of the Left function?

No. I think perhaps you have misunderstood what the Val function does - see VBE Help.

Check your declaration of LookupVal (spec. the Data Type you have assigned)

...and now ? Are you still experiencing issues ?

edit: I just also noticed you're using True in your VLookup - you might want/need to revise that also.

Post a sample with the exact code you are using.

I am not going to go through your own code again given the code I provided you with previously (way back in post #2) works without issue *

* The only revisions required to that code are related to the incorrect generation of LookupVal as described previously, ie:

a) remove the outer Val( )

b) modify data type of LookupVal variable from Long to String

Are you sure it work with no issue? The same thing happen when I use your code

Please Login or Register  to view this content.

when I change the LookupVal datatype and remove Val().

Originally Posted by isabella

Are you sure it work with no issue?

Quite.

Originally Posted by isabella

The same thing happen when I use you code

It's not the same code though is it ? Why do you have a period preceding the Left call ?

That is your code after adjustment by removing Val() and datatype.

"." It is not suppose to be there??

There is no effect if I change it to

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Same error (type mismatch) prompted.

Originally Posted by isabella

That is your code after adjustment by removing Val() and datatype.

No, I'm afraid that's not true - there is no period delimiter preceding Left in my code.

Originally Posted by isabella

There is no effect if I change it to... [with/without period]

Same error (type mismatch) prompted.

This is getting a little absurd I think - the code from post 2 revised as outlined works on your sample file without issue:

Please Login or Register  to view this content.

on that basis I've nothing further to add at this stage I'm afraid.

edit: if you're trying to re-run the code post errors then obviously that's a different issue and is not related to the suggested code [you have underlying error values which should not be present]