CODEWITHSUNDEEP
vbaexcel
user1283776
user1283776

Reputation: 21764

Replace all instances of string in formulas of cells in range?

I have a range

Dim r as range

Cells in that range may be either visible or hidden.

I want to search for all instances of $B$2 in formulas and replace them with $C$3.

What is a good way to do this?

Upvotes: 0

Views: 880

Answers (2)

user1283776
user1283776

Reputation: 21764

The following works for cells hidden by filter as well:

Sub test()

    Dim r As Range, fw As String, rw As String

    On Error Resume Next
    Set r = Range("A1:A5").SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0

    If Not r Is Nothing Then
        fw = "$B$2": rw = "$C$3"
        For Each c In r.Cells
            c.Replace what:=fw, replacement:=rw, lookat:=xlPart, MatchCase:=False
        Next c
        Set r = Nothing
    End If

End Sub

I modified the solution posted by Jeeped

Upvotes: 1

user4039065
user4039065

Reputation:

Try something like,

Dim r as range, fw as string, rw as string
on error resume next
application.calculation = xlcalculationmanual
set r = Range("A1:Z99").SpecialCells(xlCellTypeFormulas, 23)
if not r is nothing then
    fw = "$B$2": rw = "$C$3"
    r.Replace what:=fw, replacement:=rw, lookat:=xlPart, matchcase:=False
    set r = nothing
end if
application.calculation = xlcalculationautomatic

EDIT: no loop was really needed so I removed it.

Upvotes: 2

Related Questions