need help fixing vlookup script and adding second step

attached is a sample file where I am trying to assign a name in column A based on a vendor name in column B, the majority of the first step works fine except if the vendor name starts with a numeric character. Can some one offer a better method to achive what I am looking for? I listed the desired results in column G. I basically need to know how to get the lookup to work correctly for vendor names that start with a number( like on row 53) and create a second step to replace the names in column A based on the criteria in cells I24:J27 on the 'APDir' tab.

s4

Hi

The following UDF, invoked as
Sheet2!C2: =myfunc(B2,APDir!$I$24:$I$27,APDir!$I$13:$J$20)

returns the correct results except for row 9 where it brings back Jody whereas you have Lupita. Can you please give more detail on how the Mf is to be interpreted.

Please Login or Register  to view this content.

rylo

if the first two letters of the vendor name starts with Me it should be Jody if they start with Mf -Mz it should be Lupita, that's why i look at the first two left characters of the vendor name in this line.

Found.Offset(, 0).Value = WorksheetFunction.VLookup(Left(Found.Offset(, 1), 2), Rng1, 2, True)

Hi

OK, then change the function code to

Please Login or Register  to view this content.

Works for all the example items except for row 53, and when I did an edit / save on APDir!I13, it worked for that item as well.

rylo

Hi

OK, I opened the example file from #1, selected cell APDir!I13, F2, Enter.

Then I made sure that myfunc and the macro above were both in a general module in that workbook.

I changed the line

Please Login or Register  to view this content.

to be

Please Login or Register  to view this content.

as it isn't an inbuilt worksheetfunction.

It put a result into Sheet2!A53, but then errored out - probably because of the disconnect between the filled column A, and the full column B being actioned.

However, why not put in the function directly. If you don't want the function left, then have the macro run it directly into the cell(s) required, then value paste the results.

rylo

good idea, I will take that approach. thanks again.