Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Edit the range of a pivot table | Excel Worksheet Functions | |||
Edit replace within data table formula | Excel Programming | |||
Edit/Replace from Table | Excel Programming | |||
How to edit a named range | Excel Discussion (Misc queries) | |||
How do I edit a Named Range using macro's | Excel Worksheet Functions |