Multiple Criteria Find & Replace

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
 
M

muddan madhu

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
 
A

Alfred

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
 
J

JP

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top