Replace text string

[Jack Schitt]

Hi.

For each rCell.formula in
application.thisworkbook.activesheet.usedrange.cells.formula
I wish to replace every instance of the text string "string1" with the text
string "string2".
Sorry, I have ground to a halt. Help please?

 

[Bernie Deitrick]

Jack,


Application.ThisWorkbook.ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).
Replace _
What:="string1", Replacement:="string2", LookAt:=xlPart

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

dim cell as Range, sForm as String
for each cell in ActiveSheet.Cells.Specialcells(xlFormulas)
sForm = cell.Formula
sForm = Application.substitute(sForm,"string1","String2")
Cell.Formula = sForm
Next

An alternate approach would be to select a singe cell, then do Edit=>Goto
Special, select formulas

Then do Edit=>Replace
What: String1
With: String2

Turn on the macro recorder if you want code using this approach.

 

[Jack Schitt]

Thanks that worked fine.

 

[Jack Schitt]

Thanks Tom

It LOOKS as though Bernie's method did the trick (I picked it up before
yours). Is there a significant difference in these approaches?

dim cell as Range, sForm as String
for each cell in ActiveSheet.Cells.Specialcells(xlFormulas)
sForm = cell.Formula
sForm = Application.substitute(sForm,"string1","String2")
Cell.Formula = sForm
Next

An alternate approach would be to select a singe cell, then do Edit=>Goto
Special, select formulas

Then do Edit=>Replace
What: String1
With: String2

Turn on the macro recorder if you want code using this approach.