Creating a macro to search string in a range of cells and replace while leaving original

I think the best way to start is to create a list in a separate sheet which has maybe the English version in say Column A and the Spanish version in Column B of the various instructions. Attach a copy of the workbook to this thread with some sample (non sensitive data). Review the Forum Rule Page on how to attach samples. Then we could work from there to create a macro.

Hi John ,

Thanks for the prompt answer. I attached an oversimplified example of my workbook , just to get the sense of what I am trying to do. Hope this helps to clarify that.

Does this help?

Please Login or Register  to view this content.

Hi John,

I tried running it and for some reason the first time I tried it it gave me a range error (runtime error 9) so I changed the name of my ws2 to just 'translation' and it runs. However, the only thing printed out in the cell are the "\\" which are attached in the original cell at the end of the original statement. But no equivalent Spanish translation is printed out. Any ideas why?

Thanks in advance

Hi nilem,

I tried your code. When I click on the box, it gives me a copy of the original instructions in the cell but no spanish equivalent. It gives me " \Special Instruction 1\Special Instruction 2\Special Instruction 3\\Special Instruction 1\Special Instruction 2\Special Instruction 3." Is there something missing in the code that relates each special instruction to the Spanish equivalent ? Ultimately the idea is to have the following in a single cell : "Spanish SI1\Spanish SI2\ Spanish SI3\\Special Instruction1\Special Instruction2\Special Instruction3.

Thanks