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


--
beatrice25
------------------------------------------------------------------------
beatrice25's Profile: http://www.excelforum.com/member.php...o&userid=34505
View this thread: http://www.excelforum.com/showthread...hreadid=553813

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


beatrice25 Wrote:
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


Sub ReplaceData()
Dim myRange As Range
Set myRange = Sheets("sheet1").Range("B6:I45") 'change sheet1 to
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?
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 the
macro to change these just because they have a 0 or a 1 in
them?[/qoute]
the above macro will handle this.


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=553813

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


--
beatrice25
------------------------------------------------------------------------
beatrice25's Profile: http://www.excelforum.com/member.php...o&userid=34505
View this thread: http://www.excelforum.com/showthread...hreadid=553813

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


--
beatrice25
------------------------------------------------------------------------
beatrice25's Profile: http://www.excelforum.com/member.php...o&userid=34505
View this thread: http://www.excelforum.com/showthread...hreadid=553813

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

"beatrice25" wrote:


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


--
beatrice25
------------------------------------------------------------------------
beatrice25's Profile: http://www.excelforum.com/member.php...o&userid=34505
View this thread: http://www.excelforum.com/showthread...hreadid=553813

Thanks Simon, I really appreciate it

Beatrice


--
beatrice25
------------------------------------------------------------------------
beatrice25's Profile: http://www.excelforum.com/member.php...o&userid=34505
View this thread: http://www.excelforum.com/showthread...hreadid=553813