Matching up two lists of codes using VBA

I am trying to avoid using formulas here so need a VBA solution.

I am trying to match up two lists of product codes and their respective bar codes.

(I have added an example workbook to explain the problem)

In Sheet1 I have the following:

Product Codes...........Bar Codes
PCode 1.....................Bar code 1
PCode 2.....................Bar code 2
PCode 3.....................Bar code 3
PCode 4.....................Bar code 4

And in sheet 2 i have the same product codes except they have a four character suffix added on - and different bar codes in column 2.

So where a product code on sheet 1 would be 'product-code-1' on sheet 2 it would be 'product-code-1-abc'

I am trying to write a VBA code that will ignore the last four characters of the product codes on sheet2 and match them to those sheet1, and where there is a match copy the relevant barcode from sheet2 to third column on Sheet1. So it would be

Codes.................Bar Codes..........Bar Codes from Sheet2
PCode 1.....................Bar code 1............Bar code 1
PCode 2.....................Bar code 2............Bar code 2
PCode 3.....................Bar code 3............Bar code 3
PCode 4.....................Bar code 4............Bar code 4

I have been struggling with this for a few days so any help anyway can offer would be much appreciated

Fantastic! Thanks Andy that works perfectly!

I didn't know you could use formulas in the actual coding in that way.

Anyway cheers!

Originally Posted by OllieB

just for fun an approach using a recordset

Thanks Ollie that works too. Is a recordset similar to a scripting dictionary then?

Cheers dude, yeah I can do index match it was more the VBA. Your code works perfectly too though so thanks for your help

Originally Posted by TMShucks

Sorry, missed the bit about code not formula ...

I have just tried editing the formula in your code TMshucks:

Originally Posted by TMShucks

Please Login or Register  to view this content.

I tried to add IFERROR so it reads:

Please Login or Register  to view this content.

it works just as a formula when I enter it that way ( i did it to test it) so why not in the code?