使用Range.Find（）VBA处理错误 [英] Error Handling with Range.Find() VBA

问题描述

我回答了一个问题，使用此工作代码：

I answered a question with this WORKING code:

如果传真号码已经存在，此vba代码将从底部向上循环遍历所有ID ，它会将ID添加到C，D，E列中的该行（始终选择下一个空白），然后删除该行。最后，它交换列A和B，所以您在Col A中留下了传真号码，而B，C，D，E等等都是与该号码相关联的所有ID。

This vba code will loop through all IDs from the bottom up, if the fax number already exists, it will ADD the ID to that line in columns C, D, E, etc (always choosing the next blank) and then delete the line. At the end it swaps columns A and B so you are left with a fax number in Col A and Cols B, C, D, E, etc are ALL IDs associated with that number.

测试工作：

Sub RemoveDups()

Dim CurRow As Long, LastRow As Long, DestLast As Long, DestRng As Range

LastRow = Range("A" & Rows.Count).End(xlUp).Row

On Error Resume Next
 For CurRow = LastRow To 3 Step -1
     Set DestRng = Range("B2:B" & CurRow - 1).Find(Range("B" & CurRow).Value, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)
        DestRng = DestRng
     If Err > 0 Then
        Err.Clear
     Else
        DestLast = Cells(DestRng.Row, Columns.Count).End(xlToLeft).Column + 1
        Cells(DestRng.Row, DestLast).Value = Cells(CurRow, 1).Value
        Cells(CurRow, 1).EntireRow.Delete xlShiftUp
     End If
 Next CurRow
Columns("B:B").Cut
Columns("A:A").Insert Shift:=xlToRight
Application.CutCopyMode = False

MsgBox "Done"

End Sub

我的问题是这样的：为什么我需要额外的行 DestRng = DestRng ？我添加的原因是因为代码不起作用，所以我添加了一个检查，将 DestRng 转储到一个 Cells.Value 。所有的突然，代码工作，所以我做了一个简单的 DestRng = DestRng 行，以确保仍然有可能出现错误的代码。如果我注释掉该行，它将返回到无效的代码。试图找出为什么 Range.Find（）不会激活错误处理程序的原因，但是 DestRng = DestRng 将激活错误处理程序。

My question is this: Why do I need the extra line DestRng = DestRng? The reason I added it is because the code was non-functioning, so I added a check to dump DestRng into a Cells.Value. All the sudden, the code worked, so I made a simple DestRng = DestRng line to ensure there was still code that could error. If I comment out that line, it goes back to non-functioning code. Trying to figure out the reason why the Range.Find() will not activate the Error handler, but the DestRng = DestRng will activate the Error handler.

编辑：截图：

• 数据之前：
  


• 数据与 DestRng = DestRng AND EntireRow.Delete 注释掉：


• 我的完整代码的数据：

• Data Before:
• Data with DestRng = DestRng AND EntireRow.Delete commented out:
• Data with my full code:

推荐答案

如果您从一个完全空的工作表开始，然后运行：

If you start with a completely empty worksheet and run:

Sub errorTest()
    Dim r As Range
    Set r = Cells.Find(what:="something", after:=Range("A1"))
    r = r
End Sub

代码将失败

r=r

线。这是因为设置命令将r设置为无任何和

r=r

相当于：

r.Value=r.Value

这篇关于使用Range.Find（）VBA处理错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！