Macro help

[Guest]

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").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

 

[Guest]

Hi
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 HERE: 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").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

 

[Paul B]

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").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 **

 

[Tom Ogilvy]

Sub DoReplacements()
Dim sh as Worksheet
for each sh in worksheets
with sh
.Columns("C:C").Replace What:="x", Replacement:="1"
.Columns("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