VBA for looping VLookup to last row with data

**AWM1966** · 09-14-2012, 06:19 PM

In the attached workbook in column B (sheet "Upgrade Info") is a list of serial numbers in this format 326ABS3472. On the sheet "APC" is a range of APC Codes and corresponding short descriptions. The APC code is made up of the first three characters of the serial numbers in column B (sheet "Upgrade Info").

What I am trying to do is look at the first three characters in column B (sheet "Upgrade Info"), match that with the short description on the APC sheet, and return the value to column E of the Upgrade Info sheet. I will not know how many rows the sheet will have so it needs to loop to the last row containing data. The first row containing data will always be row 15.

**TMS** · 09-14-2012, 06:56 PM

=IFERROR(VLOOKUP(LEFT(B15,3),APC!$A:$B,2,FALSE),"")

Regards, TMS

**AWM1966** · 09-14-2012, 07:27 PM

I assume I was to post the above formula in column E? That did not work. I was looking to do this in VBA if possible.

**TMS** · 09-14-2012, 08:59 PM

Try:

=IFERROR(VLOOKUP(--LEFT(B15,3),APC!$A:$B,2,FALSE),"")

Just to see if the formula works. The VBA's not difficult.

Regards, TMS

**AWM1966** · 09-14-2012, 09:06 PM

That formula worked perfectly.

**TMS** · 09-15-2012, 02:55 AM

Thanks for the rep.

Here's the code:

Please Login or Register  to view this content.

Regards, TMS

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save