Fix for Windows 10 Excel 2013 Build 9926 Find and Replace freeze bug

Question

Does anyone have a better fix for the following bug I am getting in Excel 2013 on Windows 10 Enterprise Technical Preview Build 9926.

The issue occurs where trying to run a ‘Find & Replace’.

1.  Open a new workbook
2.  Type ‘Test’ into Cell A1
3.  Press ‘Ctrl+F’
4.  Click the ‘Replace’ tab
5.  Fill in ‘Find What’ with ‘Test’
6.  Fill in ‘Replace with’ with ‘ATest’
7.  Click ‘Replace All’
8.  Enjoy the freeze
9.  Click Ctrl+Alt+delete
10. Click ‘Task Manager’
11. Navigate to ‘Excel -> Find and Replace’
12. Right click ‘Find and Replace’ and click ‘Bring to front’
13. You now read the prompt saying ‘All done. We made 1 replacement’ (this step isn’t necessary but gives you your feedback prompt)
14. Right click ‘Find and Replace’ and click ‘End Task’
15. The task is now killed and you can go back to what you were wanting to do after the find and replace

I have troubleshot these steps so that I don’t have to ‘End Task’ on Excel which would make me lose all my work if I hadn’t saved before the find and replace.

If anyone has a better fix for this please share otherwise its probably just a bug that needs fixing.

Answer 1

What happens if you use a VBA replace command? This doesn't give you a prompt so it may be a viable replacement.

Cells.Replace "Test", "ATest"

Maybe do a test first to make it more robust:

If Selection.cells.Count > 1 then
    Selection.Replace "Test", "ATest"
Else
    Cells.Replace "Test", "ATest"
End if

This way if you have a selection and run it it will just run on the selection, if there is no selection then it will run on the sheet.

In the context of your sub it looks like this:

Sub find_and_replace_fix()
    Dim sFind As String, sReplace As String
    sFind = InputBox("Find what?")
    sReplace = InputBox("replace with")
    If Selection.Cells.Count > 1 Then
        Selection.Replace what:=sFind, Replacement:=sReplace, SearchOrder:=xlByColumns, MatchCase:=True
    Else
        Cells.Replace what:=sFind, Replacement:=sReplace, SearchOrder:=xlByColumns, MatchCase:=True
    End If
End Sub

You can go deeper detecting the selected tabs also if you wish.