Bug found while replacing text in formulas

Question

I discovered a very interesting bug today i.e if it is a bug.

Can you please confirm if you can replicate it? If it is a bug and has been not reported then I can file it as such. I am also ok if any of the Excel-MVPs want to file it as a bug.

Let's say in sheet1 in cell A1, you have a formula = $B$2+ $B$3. Now ensure that your cell is selected. Now paste this code in a module.

Sub Sample()
    Dim r As Range, sPre As String, sAft As String
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    sPre = "$B$2": sAft = "$C$3"

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

    If Not r Is Nothing Then r.Replace what:=sPre, _
                                       replacement:=sAft, _
                                       lookat:=xlPart, _
                                       MatchCase:=False
End Sub

Ideally the code should have worked and the = $B$2+ $B$3 should have changed to = $C$3+ $B$3 in the formula bar but it doesn't. It will work only if you step through it or if you do as mentioned in the next line

Now do one thing. Select any cell other than A1 or A2. Now if you run the code, the code works as expected.

At first I thought that my excel has gone crazy so I closed and re-started it but I was able to reproduce the above in Excel 2010 many number of times.

Then I thought it is a .SpecialCells issue but the above behavior can be observed with this code as well.

Sub Sample()
    Dim r As Range, sPre As String, sAft As String
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    sPre = "$B$2": sAft = "$C$3"

    Set r = ws.Range("A1:A2")

    r.Replace what:=sPre, _
              replacement:=sAft, _
              lookat:=xlPart, _
              MatchCase:=False
End Sub 

Are you able to replicate it?

Answer 1

I replicated your issue and got away with it by two ways:

1. Try ThisWorkbook.Save after the replace.

2. select other cell than A1 or A2 (cell selected whose formula getting replaced) after replacing formula.