Excel Macro On How To Search And Replace Cells With Multiple Values Specified in A Diffrent Sheet.

Question

I found This. But It Dosent work. Not sure what I am doing wrong.

In Sheet 1 I want to search and replace certain terms.

Such as WH with White, PK with Pink, TT with Two Tone, YE with Yellow and so on.

I have a list in sheet three with what to find in one column and what to replace in the same row in the next column.

Now I want to use those values and run it thru a macro and replace them in sheet 1

Thanks,

Can any one help me correct this code or help test it.

Sub multiFindNReplace()
    Dim myList, myRange
    Set myList = Sheets("sheet3").Range("A8:B10") 'two column range where find/replace pairs are
    Set myRange = Sheets("sheet3").Range("D1:F100") 'range to be searched
    For Each cel In myList.Columns(1).Cells
        myRange.Replace what:=cel.Value, replacement:=cel.Offset(0, 1).Value
    Next cel
End Sub

Answer 1

I think the second Sheet3 is just a typo:

Set myRange = Sheets("sheet1").Range("D1:F100") 'range to be searched