Excel find and replace not working

[albertc30]

Morning to everyone.

Hope you all had a good weekend.

I have this question for quite sometime now and it's puzzling me.

Whenever I need to search in the formulas to replace say Mon for Tue, excel displays cannot find any data to replace.

It will only work now if I close excel and do it again, then it works flawlessly.

has this happened to anyone before? Is this an excel glitch?

Thank you.

 

[steve the fish]

On the options button of the replace dialog box do you have 'match entire cell contents' ticked?

 

[Jack in the UK]

Something to play with, bit busy so not much to time to help fully sorry, try this on some test data in case and see what you think, edit as required

jiuk

Option Explicit
'// jiuk

Sub Change_SOMETHING_IN_A_Formula()
'// jiuk - written by: Jack in the UK
'// for MrExcel.com | Excel Resources | Excel Seminars | Excel Products
'// feed /forum/excel-questions/802461-excel-find-replace-not-working.html
'// 01 - Sept - 2014

Dim myORIGINAL_TEXT As String
Dim myREPLACEMENT_TEXT As String

myORIGINAL_TEXT = "Mon"
' jiuk -Mon / Monday works on both
myREPLACEMENT_TEXT = "Tue"

On Error Resume Next
Selection.SpecialCells(xlCellTypeFormulas).Replace _
            What:=myORIGINAL_TEXT, _
            Replacement:=myREPLACEMENT_TEXT, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            MatchCase:=False

'// jiuk Trap Error (if nothing found)
If Err.Number <> 0 Then
   ' MsgBox "No data found"
   GoTo myERROR_ZERO
End If

theEND_Change_SOMETHING_IN_A_Formula:
On Error GoTo 0
'// jiuk - do something else / or NOT
Exit Sub

myERROR_ZERO:
MsgBox "JIUK _ Nothing to replace"
Exit Sub
End Sub