Edit/Replace from table or named range?
 

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!

Edit/Replace from table or named range?
 

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 wrote:
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!

Edit/Replace from table or named range?
 

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!

On Sep 11, 11:38 am, JW wrote:
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 wrote:
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!- Hide quoted text -

- Show quoted text -

Edit/Replace from table or named range?
 

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

Steve wrote:
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!

On Sep 11, 11:38 am, JW wrote:
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 wrote:
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!- Hide quoted text -

- Show quoted text -