Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
I need to perform the same action on several dozen spreadsheets. I need to
replace all the Xs in column C with 1s, column D with 2s, E with 3s, etc. I recorded a macro of my doing that for 10 columns (selecting the column, replacing the text) . When I try and run the macro on the other spreadsheets, it selects the whole spreadsheet and replaces the Xs with 1s. I tried to cut out the pieces of code that I thought might be superfluous and currently have this: Columns("C:C").Select Selection.Replace What:="x", Replacement:="1" Columns("D:D").Select Selection.Replace What:="x", Replacement:="2" Columns("E:E").Select Selection.Replace What:="x", Replacement:="3" Columns("F:F").Select Selection.Replace What:="x", Replacement:="4" Columns("G:G").Select Selection.Replace What:="x", Replacement:="5" Columns("H:H").Select Selection.Replace What:="x", Replacement:="6" Columns("I:I").Select Selection.Replace What:="x", Replacement:="7" Columns("J:J").Select Selection.Replace What:="x", Replacement:="8" Columns("K:K").Select Selection.Replace What:="x", Replacement:="9" Columns("L:L").Select Selection.Replace What:="x", Replacement:="10" Can anyone help? Thanks in advnace, Anne |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
Hi
the same action on several dozen spreadsheets Do you need this process to be done for several sheets in a same book or accross several books Code bellow -- for specified sheet in this workbook: - sub ReplaceProcess: - wshNamesArr = array of sheet names to be processed. Change it. - run it. It loops throught the specified sheets and call ReplaceProcessSheet - sub replaceProcessSheet: process a single sheet Sub ReplaceProcess() Dim wshName Dim wshNamesArr() '----CHANGE HE names of sheets to be processed ---- WshNamesArr = Array("Sheet2", "Sheet 3", "Sheet4") For each wshName in WshNamesArr ReplaceProcessSheet ThisWorkbook.Worksheets(wshName) Next End Sub Sub ReplaceProcessSheet ( Wsh as Worksheet) With Wsh .Columns("C:C").Replace What:="x", Replacement:="1" .Columns("D:D").Replace What:="x", Replacement:="2" .Columns("E:E").Replace What:="x", Replacement:="3" .Columns("F:F").Replace What:="x", Replacement:="4" .Columns("G:G").Replace What:="x", Replacement:="5" .Columns("H:H").Replace What:="x", Replacement:="6" .Columns("I:I").Replace What:="x", Replacement:="7" .Columns("J:J").Replace What:="x", Replacement:="8" .Columns("K:K").Replace What:="x", Replacement:="9" .Columns("L:L").Replace What:="x", Replacement:="10" End With End Sub I hope this helps Regards, Sebastien "Anne" wrote: I need to perform the same action on several dozen spreadsheets. I need to replace all the Xs in column C with 1s, column D with 2s, E with 3s, etc. I recorded a macro of my doing that for 10 columns (selecting the column, replacing the text) . When I try and run the macro on the other spreadsheets, it selects the whole spreadsheet and replaces the Xs with 1s. I tried to cut out the pieces of code that I thought might be superfluous and currently have this: Columns("C:C").Select Selection.Replace What:="x", Replacement:="1" Columns("D:D").Select Selection.Replace What:="x", Replacement:="2" Columns("E:E").Select Selection.Replace What:="x", Replacement:="3" Columns("F:F").Select Selection.Replace What:="x", Replacement:="4" Columns("G:G").Select Selection.Replace What:="x", Replacement:="5" Columns("H:H").Select Selection.Replace What:="x", Replacement:="6" Columns("I:I").Select Selection.Replace What:="x", Replacement:="7" Columns("J:J").Select Selection.Replace What:="x", Replacement:="8" Columns("K:K").Select Selection.Replace What:="x", Replacement:="9" Columns("L:L").Select Selection.Replace What:="x", Replacement:="10" Can anyone help? Thanks in advnace, Anne |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
Anne, it should only replace what's in columns C:L, you also do not need to
select the columns to do it, this will do the same thing Columns("C:C").Replace What:="x", Replacement:="1" Columns("D:D").Replace What:="x", Replacement:="2" Columns("E:E").Replace What:="x", Replacement:="3" Columns("F:F").Replace What:="x", Replacement:="4" Columns("G:G").Replace What:="x", Replacement:="5" Columns("H:H").Replace What:="x", Replacement:="6" Columns("I:I").Replace What:="x", Replacement:="7" Columns("J:J").Replace What:="x", Replacement:="8" Columns("K:K").Replace What:="x", Replacement:="9" Columns("L:L").Replace What:="x", Replacement:="10" -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 2003 ** remove news from my email address to reply by email ** "Anne" wrote in message ... I need to perform the same action on several dozen spreadsheets. I need to replace all the Xs in column C with 1s, column D with 2s, E with 3s, etc. I recorded a macro of my doing that for 10 columns (selecting the column, replacing the text) . When I try and run the macro on the other spreadsheets, it selects the whole spreadsheet and replaces the Xs with 1s. I tried to cut out the pieces of code that I thought might be superfluous and currently have this: Columns("C:C").Select Selection.Replace What:="x", Replacement:="1" Columns("D:D").Select Selection.Replace What:="x", Replacement:="2" Columns("E:E").Select Selection.Replace What:="x", Replacement:="3" Columns("F:F").Select Selection.Replace What:="x", Replacement:="4" Columns("G:G").Select Selection.Replace What:="x", Replacement:="5" Columns("H:H").Select Selection.Replace What:="x", Replacement:="6" Columns("I:I").Select Selection.Replace What:="x", Replacement:="7" Columns("J:J").Select Selection.Replace What:="x", Replacement:="8" Columns("K:K").Select Selection.Replace What:="x", Replacement:="9" Columns("L:L").Select Selection.Replace What:="x", Replacement:="10" Can anyone help? Thanks in advnace, Anne |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
Sub DoReplacements()
Dim sh as Worksheet for each sh in worksheets with sh .Columns("C:C").Replace What:="x", Replacement:="1" .Columns("D:D").Replace What:="x", Replacement:="2" .Columns("E:E").Replace What:="x", Replacement:="3" .Columns("F:F").Replace What:="x", Replacement:="4" .Columns("G:G").Replace What:="x", Replacement:="5" .Columns("H:H").Replace What:="x", Replacement:="6" .Columns("I:I").Replace What:="x", Replacement:="7" .Columns("J:J").Replace What:="x", Replacement:="8" .Columns("K:K").Replace What:="x", Replacement:="9" .Columns("L:L").Replace What:="x", Replacement:="10" End with Next End Sub Should work -- Regards, Tom Ogilvy "Anne" wrote in message ... I need to perform the same action on several dozen spreadsheets. I need to replace all the Xs in column C with 1s, column D with 2s, E with 3s, etc. I recorded a macro of my doing that for 10 columns (selecting the column, replacing the text) . When I try and run the macro on the other spreadsheets, it selects the whole spreadsheet and replaces the Xs with 1s. I tried to cut out the pieces of code that I thought might be superfluous and currently have this: Columns("C:C").Select Selection.Replace What:="x", Replacement:="1" Columns("D:D").Select Selection.Replace What:="x", Replacement:="2" Columns("E:E").Select Selection.Replace What:="x", Replacement:="3" Columns("F:F").Select Selection.Replace What:="x", Replacement:="4" Columns("G:G").Select Selection.Replace What:="x", Replacement:="5" Columns("H:H").Select Selection.Replace What:="x", Replacement:="6" Columns("I:I").Select Selection.Replace What:="x", Replacement:="7" Columns("J:J").Select Selection.Replace What:="x", Replacement:="8" Columns("K:K").Select Selection.Replace What:="x", Replacement:="9" Columns("L:L").Select Selection.Replace What:="x", Replacement:="10" Can anyone help? Thanks in advnace, Anne |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |