Find and Replace Text in Formula on a given worksheet in an excel workbook using VBA

**welchs101** · 03-08-2012, 06:18 PM

Hi, I have an excel worksheet in a given workbook and i need to replace several text strings with new text strings which i have now defined as "named ranges".

So i have in the formulas something like this................ "texttoreplace"...........

basically text with quoest around it.

I want to replace this text with DefnName1 where DefnName1 = "texttoreplace"

I did record a macro and here is what i got

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

But i am not sure how to really incorporate this into my code. I have a list/array of text i wish to replace and an array / list of text i wish to insert in its place.

What i was hoping for is to search a given worksheet and then replace the text with my new defnd name.

Any ideas?

I have like over 200 of these to do this is why i am looking at a macro instead of doing it manually.

**JBeaucaire** · 03-08-2012, 08:32 PM

If on sheet2 you had a two column list... column A the strings to FIND and column B the strings to REPLACE, then this macro would use those string-pairs to do a FIND/REPLACE on sheet1:

Option Explicit

Sub MassReplace()
Dim vFIND As Range, v As Range

    Set vFIND = Sheets("Sheet2").Range("A:A").SpecialCells(xlConstants)

    With Sheets("Sheet1")
        For Each v In vFIND
            .Cells.Replace vFIND.Value, vFIND.Offset(, 1).Value, LookAt:=xlPart
        Next v
    End With

End Sub

More here:
Mass Replace