multiple find and replace

M

matt

Hi, I am trying to find a way to find and replace many
items at once. For example, I want to replace A with 1, B
with 2, C with 3 - Is there a way to do all of this at
once?? Thanks for the help!
 
J

Jason Morin

You could run the macro below. It assumes the data to find
is listed in col. A of Sheet1, starting in A1, and the
data to replace it with is found adjacent in col. B. This
macro finds and replaces for Sheet2.

Sub MultiReplace()

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim i As Integer

Set ws = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

For i = 1 To ws.Range("A1").End(xlDown).Row
ws2.Cells.Replace What:=ws.Cells(i, 1), _
Replacement:=ws.Cells(i, 2)
Next i

End Sub
--
Press ALT+F11, go to Insert > Module, and paste in the
code above and run.

HTH
Jason
Atlanta, GA
 
M

matt

It worked..thanks!
-----Original Message-----
You could run the macro below. It assumes the data to find
is listed in col. A of Sheet1, starting in A1, and the
data to replace it with is found adjacent in col. B. This
macro finds and replaces for Sheet2.

Sub MultiReplace()

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim i As Integer

Set ws = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

For i = 1 To ws.Range("A1").End(xlDown).Row
ws2.Cells.Replace What:=ws.Cells(i, 1), _
Replacement:=ws.Cells(i, 2)
Next i

End Sub
--
Press ALT+F11, go to Insert > Module, and paste in the
code above and run.

HTH
Jason
Atlanta, GA

.
Click to expand...
 
Top