Help/Advice regarding Find-->offset-->replace

Hi Everyone

I'm having some difficulty with VBA code where I am trying to use a range of cells as search criteria within another range. Once I find the variant I need I want to offset 1 cell to the left and replace the contents of said cell with a predefined variant which is linked to the search string.

I have tried using "cells.find" however this does not work as it does not pick up all strings in my search range, it simply glosses over duplicate values. I can get around this by using "cells.replace" however I cannot work out how to encorporate an "offset" into the code here to select my cells on the left.

I want to find all values within sheet2 range A1:A6 inside sheet 1 range C2:C7 and replace B2:B7 with new data predefined either inside the code or on sheet 2. I have attached a simplified version of the spreadsheet I'm looking to sort through for anyone to have a look. Examples of code I have been trying can be found in Sub Test() I would really appreciate any help you guys can give?

Thanks!!

Insert this as a new module:

Please Login or Register  to view this content.

Hope this helps

If your range is going to expand from C2:C100 for example and sheet2 range A1:A1000 for example then you should use this code:
With this code the range can expand and decrese and it will only used the cells with values in it

Please Login or Register  to view this content.

Hi Stojko89

Thank you very much for the code it is greatly appreciated however the code still ignors my duplicate values. It is these duplicate values which are the problem and they are something I cannot get around unfortunately. I need to find absolutely every occurance of these numbers in my range. Do you have any other ideas or suggestions?

Thanks again!

Wow...I have no idia I'll call someone that knows this stuff I hope