Edit/Replace from table or named range?

[Steve]

Hello. I have a column of data in Sheet1, column A. I need to do a
bunch of Replaces on this data. Can vba refernece a table or named
range to loop through the Replaces? The table is on Sheet2, A2:B20.
Column A in the tabls is the "Find", and Col B is the "Replace with".
Thanks!

 

[JW]

This is hard-coded, but could easily be tweaked to accomodate
anything.
Sub foofer()
For i = 2 To 20
Sheets("mainSheet").Range(Cells(1, 1), Cells(Rows.Count,
1)).Replace _
What:=Sheets("Sheet2").Cells(i, 1), _
Replacement:=Sheets("Sheet2").Cells(i, 2), _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i
End Sub

 

[Steve]

Thanks JW! How easy is it to take the above and used a named range?
I know for sure someone will monkey with the file and insert rows and
columns, which will royally screw things up!! Thanks for your help!

 

[JW]

I'm personally not big on using named ranges in workbooks that are
used by multiple people. One of them can remove the named range too
easily for me. I recommend just establishing the bottom row of the
find_replace sheet and itterate through the For...Next as many times
as needed.
Sub foofer2()
Dim rng As Range
For i = 2 To Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("mainSheet").Range(Cells(1, 1), Cells(Rows.Count,
1)).Replace _
What:=Sheets("Sheet2").Cells(i, 1), _
Replacement:=Sheets("Sheet2").Cells(i, 2), _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i
End Sub