Hi,

I have a macro that will ask the user to open a workbook, select a specific sheet and then ideally it will search all the cells in that workbook to find special characters. When it finds the characters it asks the user if they want to replace it or not, if no it will continue on and if yes it will replace it and continue on.

Issues;
A) It asks the user to confirm the replacing of each special character it finds in 1 cell, and once done with that cell it stops. It will not go to another cell until I run it again (and there are no special characters to replace in the previous cell). So I need it to loop through the entire worksheet rather than stop after its job is done on one cell.

I am also unsure how it will handle characters I have selected to not replace once it starts looping? Is there a way to ensure it only does one loop through the worksheet?

B) It seems to skip Cell A1 for some reason, not a huge deal as long as it does Cell A1 eventually, just odd.

Sub ReplaceOpenLoop()

    Dim fndList As Variant
    Dim rplcList As Variant
    Dim rFound As Range
    Dim response As VbMsgBoxResult
    Dim x As Long
    Dim vFile1 As Variant
    Dim newWB As Workbook
    Dim newWBS As Worksheet
            
    'Open the target workbook
    vFile1 = Application.GetOpenFilename("Excel-files,*.xls*", _
    1, "Select document to check for special characters", , False)

    'if the user didn't select a file, exit sub
    If TypeName(vFile1) = "Boolean" Then Exit Sub
    
    'Set workbook and the worksheet
    Set newWB = Workbooks.Open(vFile1)
    Set newWBS = newWB.Worksheets("ValidationRules-Common")
    
    fndList = Array("‘", "–", "’", "“", "”")
    rplcList = Array("'", "-", "'", """", """")

    For x = LBound(fndList) To UBound(fndList)

    'Reset the found variable and search
    Set rFound = Nothing
    Set rFound = newWBS.Cells.Find(What:=fndList(x), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False)
    
    'Check if somthing was found.
    If Not rFound Is Nothing Then
        'Ask question and get response.
        response = MsgBox("The special character: " & fndList(x) & " was found in cell " & rFound.Address(0, 0) & ". Do you want to replace it?", vbInformation + vbYesNo)

        If response = vbYes Then
            rFound.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
        End If
    End If
    Next x
End Sub
Thanks for the help,
Mattyfaz