My Search/Replace doesn't work?

There could be several reasons why your Search/Replace function in Excel is not working as expected. Here are some potential causes and solutions:

1. Text Not Found: Ensure that the text you are searching for actually exists in the selected range. If Excel returns "not found," it may be because the specified text doesn't exist in the selected cells. You can check this by manually looking for the text.
2. Search Scope: If you have specified a search scope (like a selection, module, or project) in your macro, it might be limiting the search area. If no region is selected, the current module is the default search region. You can widen the scope of the search if necessary.
3. No Text Selected: If you are trying to perform a Find or Replace operation without selecting any text, Excel will not be able to execute the operation. Make sure to select the text you want to search or replace.
4. Search String Complexity: If your search string is too long or complex, it may not be processed correctly. Try simplifying your search string to see if that resolves the issue.
5. Macro Interference: Since you mentioned that you have a macro that uses the VBA replace function, it's possible that the macro is interfering with the standard Find/Replace functionality. Review your macro code to ensure it is not inadvertently affecting the search operations.

If these suggestions do not resolve the issue, consider testing the Find/Replace function in a new workbook to determine if the problem is specific to the current workbook or the macro itself.

Hi @Chris K,

Thank you for posting your question in the Microsoft Q&A forum.

According to your concern, if the VBA script is not a suspected one, you can try checking the Find/Replace dialog settings:

• Click Ctrl+H in Excel to open the dialog box and click Options
• In Find tab, ensure Match case and Match entire cell contents are both set to OFF
• Next go to Find and Replace tab, also ensure Match case and Match entire cell contents are both set to OFF.
• If any Format is set, click the dropdown next to Format > choose Clear Find/Replace Format

Then you can try using the feature again to see if it works properly now. If you have any update regarding the issue, please share me in the comments of this port and I’ll be happy to continue supporting you.

Thank you for your patience and understanding. I'm looking forward to your response.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Duplicated

Ive confirmed that my macro that's causing this problem so Ive included a short version here in case theres anything obviously causing it. The macro works fine and has done for years btw.

One thing I can see but I cant figure out how to turn error checking back on? I thought it was "Onerror Off" but i get a syntax error

Sub ReplaceAcronymns()
'
' ReplaceAcronymns Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'

    
    Dim cel As Range
   
    
    With Application.Selection
        .Replace "(", "", xlPart, xlByColumns, False
        .Replace ")", ""
        .Replace ",", " ,"
        .Replace ". ", Chr(10) & ". "
    End With
    
    With Application.Selection
        .Replace "IMCA", "Independent Mental Capacity Assessment", , , False
        '.Replace "&", "and"
        .Replace " HART", " Homecare and Reablement Team"
        
        .Replace "PPMF", "Provider Performance Monitoring Form"
        .Replace "MCA", "Mental Capacity Assessment"
        .Replace "FREEVA", "Free from Violence and Abuse"
   --------------------------------

        .Replace " ld ", " Learning Disabilities "
        .Replace " OT ", " Occupational Therapist "
        .Replace " PA ", " Personal Assistant "
        .Replace " SM ", " Senior Manager "
        .Replace " MH ", " Mental Health "
        .Replace " SPA ", " Single Point of Access "
   End With
        
   With Application.Selection
        
        .Replace " ,", ","
        '.Replace " .", "."
        '.Replace Chr(10) & Chr(10) & Chr(10), ". "
        .Replace Chr(10) & Chr(10), Chr(10)
        .Replace Chr(10) & Chr(10), Chr(10)
        .Replace Chr(10) & Chr(10), Chr(10)
        '.Replace Chr(10), ". "
        '.Replace "..", "."
        '.Replace "..", "."
        '.Replace "..", "."
        .CheckSpelling
    End With
    
    For Each cel In Application.Selection
        On Error Resume Next
        'cel.Value = Application.WorksheetFunction.Clean(cel.Value)
        cel.Characters(Start:=1, Length:=0).Font.FontStyle = "Regular"
        cel.Characters(Start:=1, Length:=InStr(1, cel.Value, ":", vbTextCompare)).Font.FontStyle = "Bold"
    Next cel
    
End Sub

Hi @Chris K,

I have tested your VBA script in several devices, and I confirm that your VBA makes Find Replace not working properly. The reason is your VBA uses Range.Replace, which save key search settings (like LookAt, SearchOrder, etc.) whenever you run it. Since Excel remembers Replace settings globally, those saved settings are shared with the regular Find/Replace dialog which can cause it behaving differently.

About your syntax error, that's because you are having a -------------------------------- line with wrong syntax. If you want to add a line to distinguish script parts, you need to use '---------------------------- instead.

To make your script and Find Replace both work properly, you need to reset the Find/Replace state after your macro. You can try this script:

Sub ReplaceAcronymns()
 
    Dim rng As Range
    Dim cel As Range
 
    Set rng = Selection
 
    ResetFindReplaceState 
 
    With rng
        .Replace What:="(", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns, _
                 MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        .Replace What:=")", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns, _
                 MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        .Replace What:=",", Replacement:=" ,", LookAt:=xlPart, SearchOrder:=xlByColumns, _
                 MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        .Replace What:=". ", Replacement:=vbLf & ". ", LookAt:=xlPart, SearchOrder:=xlByColumns, _
                 MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    End With
 
    With rng
        .Replace What:="IMCA", Replacement:="Independent Mental Capacity Assessment", LookAt:=xlPart, _
                 SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        .Replace What:=" HART", Replacement:=" Homecare and Reablement Team", LookAt:=xlPart, _
                 SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        ' ...repeat same pattern for your other replacements...
    End With
 
    With rng
        .Replace What:=" ,", Replacement:=",", LookAt:=xlPart, SearchOrder:=xlByColumns, _
                 MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        .Replace What:=vbLf & vbLf, Replacement:=vbLf, LookAt:=xlPart, SearchOrder:=xlByColumns, _
                 MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    End With
 
    rng.CheckSpelling
 
    For Each cel In rng
        On Error Resume Next
        cel.Characters(Start:=1, Length:=0).Font.FontStyle = "Regular"
        cel.Characters(Start:=1, Length:=InStr(1, cel.Value, ":", vbTextCompare)).Font.FontStyle = "Bold"
        On Error GoTo 0
    Next cel
    
    ResetFindReplaceState  ' leave Excel in a safe Find/Replace state
 
End Sub
 
Private Sub ResetFindReplaceState()
    ' Clear any format constraints used by Find/Replace
    Application.FindFormat.Clear
    Application.ReplaceFormat.Clear
 
    ' Force Excel to re-store "safe" search settings
    On Error Resume Next
    Cells.Find What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
               SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
               MatchByte:=False, SearchFormat:=False
 
    Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _
                  MatchCase:=False, MatchByte:=False, SearchFormat:=False, ReplaceFormat:=False
    On Error GoTo 0
End Sub

I hope this helps you move forward smoothly. If you have any questions or need further assistance, please feel free to share them in the comments on this post so I can continue to support you.  

Thank you for your patience and understanding. Looking forward to your response.