Multiple Criteria Find & Replace

  • Thread starter Thread starter Alfred
  • Start date Start date
A

Alfred

Wondering if there is a script out there that can find and replace
multiple cells with different criteria.

Example:

Worksheet tab 1:
Columns
A B
Red 1
Green 2
Blue 3


Worksheet tab 2:
Columns
A B C
1 1 3
2 1 2
2 3 1

After macro/script, we would have replaced the numbers in Worksheet
tab 2 with the words from Worksheet tab 1, so Worksheet tab 2 would
look like this:

Worksheet tab 2:
Columns
A B C
Red Red Blue
Green Red Green
Green Blue Red


Thanks for any help!

Al
 
Sub findreplace()

Dim cell As Range

For Each cell In Sheets("sheet2").UsedRange
cell.Replace What:="1", Replacement:="Red"
cell.Replace What:="2", Replacement:="Green"
cell.Replace What:="3", Replacement:="Blue"
Next

End Sub
 
Thanks!


Sub findreplace()

Dim cell As Range

For Each cell In Sheets("sheet2").UsedRange
  cell.Replace What:="1", Replacement:="Red"
  cell.Replace What:="2", Replacement:="Green"
  cell.Replace What:="3", Replacement:="Blue"
Next

End Sub
Click to expand...
 
You don't need to loop, you can use the Replace function directly on
the entire range.

Sub findreplace()

With Sheets("Sheet2").UsedRange
.Replace What:="1", Replacement:="Red"
.Replace What:="2", Replacement:="Green"
.Replace What:="3", Replacement:="Blue"
End With

End Sub
 
Back
Top