Using VBA search and replace in formulas

[Foodo]

Hi Excel Experts,

I am fairly new to VB/Macro so any help would be much appreciated. I am trying to create a macro that will search through a worksheet for a particular portion of a formula and replace with something else. Below is what I have built so far:



For Each cell In Selection.SpecialCells(xlConstants, xlTextValues)

Sub ReplaceFormula()

Dim cell As Range
For Each cell In Selection.SpecialCells(xlConstants, xlTextValues)




Cells.Find(What:="OLD", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Replace What:="OLD", Replacement:="NEW", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.FindNext(After:=ActiveCell).Activate


Next cell
End Sub


It seems to work if "OLD" is in text, but if it is in a formula (e.g., "=OLD"), it will ignore. Help please?

 

[Michael M]

MAybe this

Sub MM1()
    Dim Findtext As String, Replacetext As String
    Findtext = "old"
    Replacetext = "new"
    Selection.Replace what:=Findtext, replacement:=Replacetext, lookat:=xlPart, MatchCase:=True

End Sub

But rather than select why not use the column to be the the range

Sub MM1()
    Dim Findtext As String, Replacetext As String
    Findtext = "old"
    Replacetext = "new"
     Columns("A").Replace what:=Findtext, replacement:=Replacetext, lookat:=xlPart, MatchCase:=True

End Sub