Using VBA to replace multiple cells in a sheet with a set of values

[panos_gyzi]

Hello All,
I am running Excel 2010 in Windows 7 – Home premium
I am facing the following issue. I have a number of excel files where I will need to find a cell with a specific value (there could be multiple instances of this value in the same sheet) and replace them all with a different value. I do that with Ctrl+F and opting to replace all. My problem is that there are many values to replace and it is cumbersome. I tried to put together a macro for the first excel file, but I was not successful. Any help would be much appreciated.
N.B. I normally have the two columns next to each other. One column with the values I copy and the other one has the values I want to replace.
Thank you in anticipation of your assistance.
Panos

 

[dermie_72]

you could try this code

Sub find_replace()

Dim A As String
Dim B As String
    A = InputBox("What string are you looking for")
    B = InputBox("What String do you wish to replace it with?")
    Cells.Replace What:=A, Replacement:=B, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
End Sub

 

[panos_gyzi]

Hello Dermie_72

I used your code and I got the following two prompts...
"What string are you looking for"
"What String do you wish to replace it with?"
I could not go around.

Please find below the link to the image of the excel. Columns C and D are the mapping table. Essentially, I am looking to replace D with C in the whole sheet.

Thank you for your quick response,

Panos


https://imageshack.com/i/pc0fdiFOj

 

[dermie_72]

you need to tell it what you're looking for and what you're wanting to replace it with.
This allows you to use the same code for ANY Text, not just the one change