replacing macro?

B

beatrice25

Hi Everyone,
I have a sheet (actually hundreds of sheets)with data in B6 to I45,
inside it i need to make lots of replacements, e.g.
0 becomes DSR0
1 becomes DSR1
C1,C2,C3,C4,C5 become CTRL
How can I use a macro to do this and be able to add lines to it if i
want to do more replacements?

Also some cells make contain 10 or 11 for example, I do not want the
macro to change these just because they have a 0 or a 1 in them?

Any ideas?
I am still searching old questions in the forum to find the answer


Thanks for any help

B
 
M

Mallycat

It depends on the level of automation you want. A VBA macro would d
it

I have a sheet (actually hundreds of sheets)with data in B6 to I45
inside it i need to make lots of replacements, e.g.
0 becomes DSR0
1 becomes DSR1
C1,C2,C3,C4,C5 become CTRL
How can I use a macro to do this
Click to expand...


Sub ReplaceData()
Dim myRange As Range
Set myRange = Sheets("sheet1").Range("B6:I45") 'change sheet1 t
your sheet name
For Each cell In myRange
Select Case cell
Case 0
cell.Value = "DSR0"
Case 1
cell.Value = "DSR1"
Case C1, C2, C3, C4, C5
cell.Value = "CTRL"
End Select
Next cell
End Sub


and be able to add lines to it if i want to do more replacements?
Click to expand...
this is harder. How will you know if you need more lines?
Also some cells make contain 10 or 11 for example, I do not want th
macro to change these just because they have a 0 or a 1 i
them?[/qoute]
the above macro will handle this
Click to expand...
 
B

beatrice25

Thanks MallyCat I will gove this a go,
Do I have to rename the sheet in the macro whenever I need to run it o
is there a way that I can get the macro to step through my hundreds o
sheets and make the replacements on the same location(B6:I45) on eac
sheet?
 
B

beatrice25

Thanks MallyCat I will gove this a go,
Do I have to rename the sheet in the macro whenever I need to run it o
is there a way that I can get the macro to step through my hundreds o
sheets and make the replacements on the same location(B6:I45) on eac
sheet?
 
S

SimonCC

From Mallycat's code,

below the line "Dim myRange As Range"
insert line "For Each Worksheet In Worksheets"

change the line "Set myRange = Sheets("sheet1").Range("B6:I45")"
to line "Set myRange = Worksheet.Range("B6:I45")"

below the line "Next cell"
insert line "Next Worksheet"

That should do it for every sheet.

-Simon
 
Top