Excel VBA替换失败 [英] excel vba replace failure

查看：141
本文介绍了Excel VBA替换失败的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我运行以下宏已有好几年了，直到最近.

在过去的几周里，我不得不手动将其更改为正确的工作表，然后才能正常工作.今天它停止这样做了，所以我逐步完成了&看到它在第一个replace语句之后就退出了，不管是否有要替换的数据.

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

是否注释掉On Error GoTo 0似乎没有什么区别，坦率地说，我不知道Set r = Cells.Find( ...语句的作用是什么.

我不确定100％，但是我认为该功能在升级到Win 10之后的一段时间内可以正常工作.

解决方案

我不能说我有你的答案，但是我可以回答几点，并给你一些修改的尝试.

您应该将On Error Resume Next和On Error Goto保留为0，因为它们是围绕该语句的失败陷阱，您不知道为什么会出现该错误.如它所说，它正在重置查找/替换"默认值.我不知道这里是否需要它们，或者不知道是否有必要在捕获空单元格时进行故障捕获，但是将其保留在这里并没有什么害处，但是重置错误处理程序很重要. >

我会摆脱选择数据表的声明，这是不必要的，而应更加明智地使用Withs.

这是我认为值得修改的代码

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

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.

解决方案

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

这篇关于Excel VBA替换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆