VBA.Find对象变量未设置错误

Question

好的，所以我试图创建一个宏，它基本上就像一个vlookup，在工作表2中的a列中逐单元格搜索相同的值，然后将该行中的所有信息复制到工作表1的第一个打开列。

基本上我不知道我在做什么，但它有95%的功能。我唯一的问题是，一旦它遇到工作表1列a中的值，而它在工作表2列a中找不到该值。我该怎么做才能跳到下一个值？

我的如果……那么……否则就是不顾一切地试图跳过这个值，显然它不起作用。

Sub ProLookUp()
Dim ColALastRow As Long
Dim ColALastRow2 As Long

ColALastRow = Worksheets(1).Columns("A:A").End(xlDown).Row
MsgBox ColALastRow
ColALastRow2 = Worksheets(2).Columns("A:A").End(xlDown).Row
MsgBox ColALastRow2
Dim i As Long
Dim Pro As String
Dim Pro2 As Long
For i = 1 To ColALastRow
Pro = Worksheets(1).Cells(i, 1).Value
'With Worksheets(2).Range("A1:A" & ColALastRow2)' 'ignore this part'
With Worksheets(2).Range("A1:A10000")
'the below is where my issue is, once it finds a value in column A that it
'cannot match in sheet 2 it returns the error
'Object variable or With block variable not set
If Pro = .Find(Pro, LookIn:=xlValues).Value Then
    Pro2 = .Find(Pro, LookIn:=xlValues).Row
Else
    i = i + 1
End If

    Dim LastColA As Integer
    Dim CopyRange As Range
    Dim a As Range
    Dim b As Range
        With Worksheets(2)
            LastColA = .Cells(Pro2, .Columns.Count).End(xlToLeft).Column
            Set a = .Cells(Pro2, 2)
            Set b = .Cells(Pro2, LastColA)
            Set CopyRange = Range(a, b)
        End With
    Dim PasteRange As Range
    Dim LastColumnB As Integer
        With Worksheets(1)
            LastColumnB = .Cells(i, .Columns.Count).End(xlToLeft).Column
            LastColumnB = LastColumnB + 1
            Set PasteRange = .Cells(i, LastColumnB)
            MsgBox PasteRange.Address
        End With
Worksheets(2).Select
    CopyRange.Select
    Selection.Copy
Worksheets(1).Select
    PasteRange.Activate
    ActiveCell.PasteSpecial
End With    
Next i
End Sub

Answer 1

我重新编写了一些其他代码。

您的i=i+1的If语句不会像您认为的那样起作用。

我将查找结果加载到一个范围变量中。如果查找没有找到任何内容，则得到的范围变量将是Nothing。由于不能在Nothing上调用任何方法，因此会引发错误91。为了解决此问题，请在If块中测试Nothing，以避免出现错误。

我们测试以确保范围变量Is Not Nothing，然后做这些事情。如果找到了Nothing，则它跳过代码并直接转到Next i。

通过尝试使用if将1添加到i，将不会引发For循环的下一次迭代。代码仍然会尝试运行，然后迭代，从而实际跳过行。

不需要激活图纸和范围，只需复制和粘贴即可。

Sub ProLookUp()
Dim ColALastRow As Long
Dim ColALastRow2 As Long

ColALastRow = Worksheets(1).Columns("A:A").End(xlDown).Row
MsgBox ColALastRow
ColALastRow2 = Worksheets(2).Columns("A:A").End(xlDown).Row
MsgBox ColALastRow2
Dim i As Long
Dim Pro As String
Dim fnd As Range
Dim Pro2 As Long
For i = 1 To ColALastRow
    Pro = Worksheets(1).Cells(i, 1).Value
    'With Worksheets(2).Range("A1:A" & ColALastRow2)' 'ignore this part'
    With Worksheets(2).Range("A1:A10000")
        'the below is where my issue is, once it finds a value in column A that it
        'cannot match in sheet 2 it returns the error
        'Object variable or With block variable not set
        Set fnd = .Find(Pro, LookIn:=xlValues)
    End With
    If Not fnd Is Nothing Then
        Pro2 = fnd.Row
        Dim LastColA As Integer
        Dim CopyRange As Range
        Dim a As Range
        Dim b As Range
        With Worksheets(2)
            LastColA = .Cells(Pro2, .Columns.Count).End(xlToLeft).Column
            Set a = .Cells(Pro2, 2)
            Set b = .Cells(Pro2, LastColA)
            Set CopyRange = Range(a, b)
        End With
        Dim PasteRange As Range
        Dim LastColumnB As Integer
        With Worksheets(1)
            LastColumnB = .Cells(i, .Columns.Count).End(xlToLeft).Column
            LastColumnB = LastColumnB + 1
            Set PasteRange = .Cells(i, LastColumnB)
            MsgBox PasteRange.Address
        End With

        CopyRange.Copy PasteRange
    End If

Next i
End Sub