Bug found while replacing text in formulas

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.

While many alternatives have been suggested in the other answers for example

1. Select another cell via code
2. Save the workbook

If I do not want to select the cell or save the workbook then is there an alternate way which is better than the above two? Yes, there is. Just tried this and it works

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:A3").SpecialCells(xlCellTypeFormulas)    On Error GoTo 0    If Not r Is Nothing Then r.Replace what:=sPre, _                                       replacement:=sAft, _                                       lookat:=xlPart, _                                       MatchCase:=False    r.Formula = r.FormulaEnd Sub

But the question still remains that the Formula Bar should have updated in the original scenario but it doesn't

I'm partly able to replicate it, also Excel 2010.

If I run the macro with the cell selected, using the Run option within the Visual Basic Editor, the value in the cell changes to reflect the new formula, but the formula in the formula bar doesn't show as updated. But it must be updated because the result changed. If I click out of the cell and back in, the updated formula appears and shows that the search/replace worked.

If I step through the macro in the VBA window, the formula bar does show as updated while the macro runs.

If I run the macro from the Excel window, using Macros -> View -> Run, the formula bar does show as updated while the macro runs.

If I add r.Select to the last line of the macro, running it from VBA works.

If I run the macro so it does not update the formula bar, then click into the formula bar, the formula bar shows the old formula but the cell content changes to show the new formula instead of the answer.

Edit: The behaviour appears the same in Excel 2013