0

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?

Community
  • 1
  • 1
user1283776
  • 19,640
  • 49
  • 136
  • 276

2 Answers2

2

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.

  • your `on error resume next` is wrongly placed :) it should be right before `set r = Range("A1:Z99")...` and then `On Error Go To 0` right after that :) – Siddharth Rout Nov 22 '14 at 05:38
  • BTW, your code led me to an interesting [discovery](http://stackoverflow.com/questions/27074661/bug-found-while-replacing-text-in-formulas) :D – Siddharth Rout Nov 22 '14 at 06:26
  • Q1: Why do you write 23 as a second parameter in the SpecialCells method? Does it change the default behavior or is it just for clarity? Q2: Why do you set application.calculation to manual? Is that relevant? Q3: Why does the method for on hidden cells? I have previously used Range.Find and that seemed to only work for visible cells. It seems inconsistent in Excel to me that .Replace works on hidden cells and .Find only works on visible cells. Here is an example of a command that only works on visible cells: lastRowOutput = Sheets("Output").Cells.Find("*", [A1], , , xlByRows, xlPrevious).row – user1283776 Nov 22 '14 at 11:38
  • The solution is very interesting but does not search cells that have been hidden by a filter. – user1283776 Nov 22 '14 at 15:13
  • 1
    @user1283776 - A1. In this case it just is just for clarity as 23 is the default. It come from xlErrors + xlLogical + xlNumbers + xlTextValues. Q2. If you were going to modify all the formulas in a worksheet why *wouldn't* you set the calculation to manual first and recalculate after the modification was done? Q3. It seems that I was working with *hidden* cells and you were working with *filtered* cells. –  Nov 22 '14 at 19:29
1

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

user1283776
  • 19,640
  • 49
  • 136
  • 276