excel vba replace failure

Question

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.

manually change to the proper sheet...tSHeet = ActiveSheet.Name guessing that's why... — findwindow
– findwindow, Commented Nov 19, 2015 at 22:02
comment out the on error resume next and see where the error happens — Scott Craner
– Scott Craner, Commented Nov 19, 2015 at 22:02
tSHeet = ActiveSheet.Name is only used to return to the calling sheet when called from elsewhere. Commenting out On Error Resume Next has no affect. — Deina Underhill
– Deina Underhill, Commented Nov 19, 2015 at 22:08

Bob Phillips · Accepted Answer · 2015-11-24 21:11:00Z

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

Thank you, Bob! That looks much more elegant that my code, and should run faster.

Deina Underhill · Accepted Answer · 2015-11-24 18:12:24Z

0

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!

answered Nov 24, 2015 at 18:12

Deina Underhill

5871 gold badge10 silver badges24 bronze badges

Collectives™ on Stack Overflow

excel vba replace failure

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related