In Excel2000, I need to write a macro that will edit replace many
strings. We changed our account string and I need to replace at least
100 different strings. For example, 2010 becomes 4010, 3526 becomes
5513. I have about 100 of these changes that I want to put into a
macro so that when I run it in any workbook it makes all of these
changes Do I just keep using Edit-Replace or do I use Case . What is
the best way of doing this?

Thanks

Edit - replace going through you list of replacements. Insure your
replacements are made so the string being replaced remains unique.


You can get the replace code using the macro recorder.
--
Regards,
Tom Ogilvy


"snax500" wrote:

In Excel2000, I need to write a macro that will edit replace many
strings. We changed our account string and I need to replace at least
100 different strings. For example, 2010 becomes 4010, 3526 becomes
5513. I have about 100 of these changes that I want to put into a
macro so that when I run it in any workbook it makes all of these
changes Do I just keep using Edit-Replace or do I use Case . What is
the best way of doing this?

Thanks

Hi Max,

Try something like:

'=============
Public Sub Tester1()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim iLastRow As Long
Dim arrIn As Variant
Dim arrOut As Variant
Dim i As Long

Set WB = Workbooks("MyBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

arrIn = Array("Anne", "Ben", "Carol", _
"David", "Ewan") '<<==== CHANGE
arrOut = Array("Red", "Blue", "Green", _
"Yellow", "Brown") '<<==== CHANGE

iLastRow = SH.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A2:A" & iLastRow) '<<==== CHANGE

On Error GoTo XIT
Application.ScreenUpdating = False
For i = LBound(arrIn) To UBound(arrIn)
rng.Replace What:=arrIn(i), _
Replacement:=arrOut(i), _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Next i

XIT:
Application.ScreenUpdating = True
End Sub
'<<=============


---
Regards,
Norman


"snax500" wrote in message
oups.com...
In Excel2000, I need to write a macro that will edit replace many
strings. We changed our account string and I need to replace at least
100 different strings. For example, 2010 becomes 4010, 3526 becomes
5513. I have about 100 of these changes that I want to put into a
macro so that when I run it in any workbook it makes all of these
changes Do I just keep using Edit-Replace or do I use Case . What is
the best way of doing this?

Thanks

Try this. Maker new worksheet called replace and put old values in column A
and new values in Column B. Then select worksheet to change and run macro.



Sub replaceEverything()

'put replacements in column A (old)and B (new) in sheet called replace
'make worksheet to change the active worksheet

LastRow = Sheets("replace").Cells(Rows.Count, "A").End(xlUp).Row

ActiveSheet.Cells.Select
For RowCount = 1 To LastRow

With Selection
OldData = Sheets("replace").Cells(RowCount, "A")
NewData = Sheets("replace").Cells(RowCount, "B")

Set c = .Find(OldData, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = NewData
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

Next RowCount

End Sub


"snax500" wrote:

In Excel2000, I need to write a macro that will edit replace many
strings. We changed our account string and I need to replace at least
100 different strings. For example, 2010 becomes 4010, 3526 becomes
5513. I have about 100 of these changes that I want to put into a
macro so that when I run it in any workbook it makes all of these
changes Do I just keep using Edit-Replace or do I use Case . What is
the best way of doing this?

Thanks