excel vba replace failure

问题:

I have been running the following macro for several years with no problem until recently.

The past few weeks I have had to manually change to the proper sheet before it would work. Today it stopped doing that, so I stepped through it & see that it is exiting out after the very first replace statement, whether there is data to replace or not.

Sub Clean_Phone()
'
' Clean_Phone Macro
'
' Last Update - 5 Feb 2015
'
    Dim tSHeet As String
    Dim r As Range
    On Error Resume Next    ' restore Find/Replace settings to default
    Set r = Cells.Find(What:=vbNullString, LookIn:=xlFormulas, _
                       SearchOrder:=xlRows, LookAt:=xlPart, MatchCase:=False)
    '    On Error GoTo 0
    tSHeet = ActiveSheet.Name

    Sheets("Data").Select  ' DataTbl is 15 col x > 1100 row
    With Sheets("Data").Range("DataTbl[[Latitude]:[Longitude]]")
        .Replace What:="°", Replacement:=vbNullString, LookAt:=xlPart
    End With

    Sheets("Data").Select
    With Sheets("Data").Range("DataTbl[[Phone]:[Phone2]]")  ' DataTbl is 15 col x >1100 row
        .Replace What:=" ", Replacement:=vbNullString, LookAt:=xlPart
        .Replace What:=")", Replacement:=vbNullString, LookAt:=xlPart
        .Replace What:="-", Replacement:=vbNullString, LookAt:=xlPart
        .Replace What:="(", Replacement:=vbNullString, LookAt:=xlPart
        .Replace What:=".", Replacement:=vbNullString, LookAt:=xlPart
    End With

    Range("DataTbl[[Phone]:[Phone2]]").NumberFormat = "[<=9999999]###-####;(###) ###-####"

    With Sheets("Data").Range("DataTbl[Address]")
        .Replace What:=" nw ", Replacement:=" NW ", LookAt:=xlPart
        .Replace What:=" ne ", Replacement:=" NE ", LookAt:=xlPart
        .Replace What:=" se ", Replacement:=" SE ", LookAt:=xlPart
        .Replace What:=" sw ", Replacement:=" SW ", LookAt:=xlPart
    End With

It seems to make no difference if the On Error GoTo 0 is commented out or not, and, frankly, I haven't a clue what purpose the Set r = Cells.Find(... statement serves.

I'm not 100% sure, but I think the function worked properly for a time after I upgraded to Win 10.

回答1:

I can't say that I have your answer, but I can answer a couple of points and give you a slight amendment to try.

You should leave the On Error Resume Next and On Error Goto 0 as they are a failure catch around that statement that you don't know why it is there. As it says, it is resetting the Find/Replace defaults. I have no idea if they are required here or not, or if the failure catch is necessary as it is searching for an empty cell, but it does no harm to leave it there, but it is important to reset the error handler.

I would get rid of the statement that selects the Data sheet, it is unnecessary, and use the Withs more judiciously.

This is the amended code which is worth trying in my view

Sub Clean_Phone()
'
' Clean_Phone Macro
'
' Last Update - 5 Feb 2015
'
Dim tSHeet As String
Dim r As Range

    On Error Resume Next
    ' restore Find/Replace settings to default
    Set r = Cells.Find(What:=vbNullString, LookIn:=xlFormulas, _
                   SearchOrder:=xlRows, LookAt:=xlPart, MatchCase:=False)
    On Error GoTo 0
    tSHeet = ActiveSheet.Name

    With Sheets("Data")

        With .Range("DataTbl[[Latitude]:[Longitude]]")
            .Replace What:="°", Replacement:=vbNullString, LookAt:=xlPart
        End With

        With .Range("DataTbl[[Phone]:[Phone2]]")  ' DataTbl is 15 col x >1100 row
            .Replace What:=" ", Replacement:=vbNullString, LookAt:=xlPart
            .Replace What:=")", Replacement:=vbNullString, LookAt:=xlPart
            .Replace What:="-", Replacement:=vbNullString, LookAt:=xlPart
            .Replace What:="(", Replacement:=vbNullString, LookAt:=xlPart
            .Replace What:=".", Replacement:=vbNullString, LookAt:=xlPart

            .NumberFormat = "[<=9999999]###-####;(###) ###-####"
        End With

        With .Range("DataTbl[Address]")
            .Replace What:=" nw ", Replacement:=" NW ", LookAt:=xlPart
            .Replace What:=" ne ", Replacement:=" NE ", LookAt:=xlPart
            .Replace What:=" se ", Replacement:=" SE ", LookAt:=xlPart
            .Replace What:=" sw ", Replacement:=" SW ", LookAt:=xlPart
        End With
    End With
End Sub

回答2:

I found the problem.

I had a completely different workbook open and it was interfering with this one. I'm not sure how -- they didn't share any modules, but when I closed it, this one started acting right.

Thank you to everyone for your efforts!