VBA V Lookup

Hello All!!

Ok - The below code is a V Lookup written in VBA. It works perfectly, other than, when the lookup table does not have the exact value, it picks a value from the lookup table and fills in the corresponding cell.

Example: If "606" = "Mary" in the lookup table, and the lookup came across "505" it may still fill in the corresponding cell with "Mary" with no apparent reason or logic of what it chooses. I should provide a NA error or something other than a random value picked from the lookup table. Hope this makes sense. Thanks to anyone who can help identify this issue.

Please Login or Register  to view this content.

Try:

Please Login or Register  to view this content.

Then, if the VLOOKUP fails, you should get NA.


Regards, TMS

Try

Please Login or Register  to view this content.

comment out the .Value = .Value line and see what formula its putting in those cells.

You are processing with two sheets in the code: "CallData" & Sheet16. But you have:
With Range("N2:N" & LR) which will operate on the currently selected sheet.
You should have:
With Sheet("x").Range("N2:N" & LR)...

Also, I would suggest not using the sheet's code name (Sheet16), but the tab name instead; since the code name can change depending on sheet additions/deletions. The tab name won't .

Eliminate the For loop, use the keyword Set and insert some S's

Please Login or Register  to view this content.

That was probably your issue all along, since vLookupTable is a Variant, and you weren't using the Set keyword, you were getting a variant array, and VLOOKUP is happier with ranges than variant arrays. Declaring it as Range isn't necessary (but it does save resources). The Set is needed to assign a Range object to a variable.