Hi everyone.
I have a column B with 726 book titles. I need to find the ones that have country codes in brackets, eg. (ES) and replace the text inside the brackets with a full country name, e.g. (Spain). I came across the macro below. It seems to work, but the problem is, it would only work if I had just (ES) in my cell and nothing else. I need a code that will replace this (ES) for (Spain) and other country names but the book title will remain. For example if the title of a book is: "Workbook Second Edition 1 (PL)" I would like to change it to "Workbook Second Edition 1 (Poland)".
In Sheet 2, I have created a column A with a full list of country codes in brackets and column B with a full list of the corresponding country names in brackets. These are the values that the macro should look for in my Column with book titles (in sheet 1).
Could anyone please help me modify this macro or suggest a new one in order to make it work?
Sub ReplaceValues()
Dim cell As Range
'will go 1-by-1through the cells in the range A1:A10 on Sheet1
For Each cell In Sheets("Sheet1").Range("A1:A10")
'Any value on Sheet2 within the range B3:B15 that matches
'the value of the current cell being checked on Sheet1
'will be replaced by the value of the corresponding cell in column B on Sheet1
Sheets("Sheet2").Range("B3:B15").Replace cell.Value, cell.Offset(, 1).Value, vbTextCompare
Next cell
End Sub
Thank you in advance.
Bookmarks