VLookup error in Excel 2011 for Mac Macro

Hi,
I have tried for hours to get a VLookup call in a macro to work. I am not experience VBA programmer, but I am very experienced in C++. Even a hard coded argument function does not work. I get various errors like 1004, 2015, etc

Attached is a simple test worksheet that is labeled and has the code.

The problem I want to solve is to take a delimited list as a string (e.g. "a,b,c,d") and extract each delimited item and use it to lookup in a table , then construct a delimited list of the looked up values. So If I had a table like a,1 b,2 c,3 d,4 then a,b,c,d would turn into 1,2,3,4
The list can be an arbitrary length.

The code supplied breaks the problem down into counting the terms (which works), Extract each term by an index with 1 being the first term (also works). The final step is to put the extract term into vlookup with the rest of the arguments (the table range, the column number and range lookup boolean) simply passed by the caller. The test spreadsheet show everything working in the spreadsheet (hard coded in terms of using the term values in vlookup). In the macro however, it just does not work. I am chalking it up to my inexperience with VBA. I appreciate the help.

The macro is under Module

Here's one method.

Please Login or Register  to view this content.

Hi,

Thanks for this, really appreciate it. Its much more elegant approach. It however is not working and I get a comma separated list of "N/A,s". I have incorporated the split routine which makes the the other support functions dead simple and it works. Still the same problem, the lookup does not seem to access the range. I have uploaded the revised spreadsheet with all the changes VLookup1.xlsm. You will see I tried the VLookup using arrTerms and that still does not work either. The N/A list is clearly visible. Does the spreadsheet work for you. I am on a MAC 10.7.5

Thanks again

Cell D5 is a cell (range) that contains a string. The first argument for CVSLookup should be a Range-type instead of String-type.

Please Login or Register  to view this content.

This would be how I might name and declare the arguments.

Please Login or Register  to view this content.

Note: This version of CSVLookup doesn't use the last argument RangeLookup1
So the formula would change from this...
=CSVLookup(D5,",",A5:B19,2,0)
To this...
=CSVLookup(D5,",",A5:B19,2)

Hi,

Thanks again. You have the code down to a very elegant size. I copy it in completely. I made the change to the spreadsheet as well for the argument. No joy. Still getting N/As. I am starting to believe something else is going on. Clearly the lookup works on the spreadsheet. I have attached the latest updated sheet which is just your code. I made a change to the function in the sheet to match the new signature of the function in the VBA code.

When I step through it, It fails to find the lookup value. (it would fault for the worksheetfuntion.vlookup). The issue is on the line for the range lookup. Accessing the csv item array element works, since it is exactly what we do on the CSVGetItem. I think it is accessing the range of cells to lookup. Is there easy code that would output the lookup cells (A5:B19). I would guess that that access would fail.

Does the sheet work for you?

It does work for me but I have a Excel 2003 on a PC. I would think it should work on a Mac as the code isn't doing anything tricky.

It's not erroring. It's just not finding a match. One guess is the values in D5 are text and the values in A5:A19 are numeric. This shouldn't matter with the .Find method, but maybe it does on a Mac for some reason.

As a quick test, try this...

Please Login or Register  to view this content.

This will convert the text value to numeric. Maybe that will help it find the match.

Hi Sorry for the delay, I had to fly to Florida. I gave this a try and it did not work. Anyway the lookup value could be anything, not just numbers. If this works on a PC, then its not the code! My original code (as poor as it was) was basically faulting on the VLookup as well.

I just do not understand at this point. If I implemented my own VLookup I guess it would point the way, but thats somewhat crazy. You have shown how elegant the code can be. When I get back to the office, I wil try this on a PC, if it works, then its something about Excel on a MAC which would be a bummer. Is there a way to ask the Excel team by supplying them the code and spreadsheet?

This uses a direct comparison method. I would think this should work on a Mac. It works on my PC with your example workbook.

Please Login or Register  to view this content.

So that works! Sorry for the delay, I went to Florida for a few days!. This is solved. The larger question is why did none of the other things work on a MAC? My last question will be how do I find that out. I consider it a bug in Excel on the MAC since calling a worksheet function to do what you provided code to do is a problem. In closing, I really appreciated the help and learned quite a bit from the process. I'll close the thread after your response

I don't know.