我有一个宏,搜索和复制一些日期从一个工作表回到另一个,如果该值被找到,并重复为每一行不是空白。
在我当前的文档中,大约有150行,以下宏运行良好,但随机抛出错误:
运行时错误'91':Object变量或With块变量未设置
这是宏:
Sub Update()
Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.StatusBar = "Cleaning New Roles..."
Sheets("new").Select
Range("A2").Select
Do Until IsEmpty(ActiveCell)
Dim SearchValue As String
SearchValue = ActiveCell.Value
Sheets("Old").Select
On Error GoTo Error_handler
Cells.Find(What:=SearchValue, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=False).Activate
Range("R" & Selection.Row & ":T" & Selection.Row).Select
Selection.Copy
ActiveCell.Offset(0, -1).Select
Sheets("new").Select
Range("R" & Selection.Row).Select
Selection.PasteSpecial Paste:=xlPasteValues
Error_handler:
Sheets("new").Select
Range("A" & Selection.Row).Select
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub
调试突出显示Cells.Find
行。
我不明白为什么前100行是OK的,然后它就坏了。
不用那么多.Select
试试。
Sub Update()
Dim SearchValue As String, rFnd As Range, o As Long
Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.StatusBar = "Cleaning New Roles..."
With Sheets("new")
o = 0
Do Until IsEmpty(Range("A2").Offset(o, 0))
SearchValue = vbNullString
SearchValue = .Range("A2").Offset(o, 0).Value
With Sheets("Old")
On Error GoTo Error_handler
Set rFnd = .Cells.Find(What:=SearchValue, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=False)
Sheets("new").Range("R2").Offset(o, 0).Resize(1, 3) = _
.Range("R" & rFnd.Row).Resize(1, 3).Value
End With
Error_handler:
o = o + 1
Loop
End With
Application.ScreenUpdating = True
Application.StatusBar = vbNullString
End Sub
请参阅如何避免在Excel VBA宏中使用选择以获取更多方法,以摆脱依赖于选择和激活来完成目标。
如果执行成功,则Find()
函数返回Range
对象。你假设它总是这样,并且你将Activate()
调用链接到末尾:
Cells.Find(What:=SearchValue, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=False).Activate
如果Find()
失败,这将失败。相反,将返回值赋给Range
对象并测试是否成功:
Dim r As Range
Set r = Cells.Find(What:=SearchValue, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=False)
If Not r Is Nothing Then
' Find successful
End If
如果没有找到搜索字符串,通常会出现此错误。VBA不会出现"找不到您要找的东西",而是向您抛出此错误消息。因此,您需要确保您正在为VBA寻找的所有内容实际上都在那里,或者捕获错误并解决如果不是的话该怎么办。