对象变量或块变量在尝试执行 vlookup 时未设置 VBA Excel

Question

我正在尝试在同一工作簿中从一个工作表到另一个工作表执行 vlookup。但是收到运行时错误">对象变量或未设置块变量"。如果有人可以看看并在这里帮助我。几天就被困在这里了

此外，我无法在立即块中打印特定范围内的值以进行调试

Dim c1 As Range  
Dim Values1 As Range  
Dim Values2 As Range  
Values1 = Sheet2.Range("A2:A14")  'Getting the error here
Values2 = Sheet1.Range("A2:C14")  
AC_Row = Sheet2.Range("H1").Row  
AC_Col = Sheet2.Range("H1").Column
For Each c1 In Values1  
Debug.Print c1.Value  'Is this correct way to print??
Sheet2.Cells(AC_Row, AC_Col) = Application.WorksheetFunction.VLookup(c1, Values2, 2, True)  
 AC_Row = AC_Row + 1  
Next c1  

Answer 1

请使用Set关键字设置Range对象，如下所示：

Set Values1 = Sheet2.Range("A2:A14")  
Set Values2 = Sheet1.Range("A2:C14")

Answer 2

谢谢伙计们，我能够达到预期的结果。请在下面找到我的代码。

 Dim rng As Range
 Dim rng1 As Range
 Dim c1 As Range
 Dim datavalue_Target As Variant
 Dim datavalue_OuterB As Variant
    Dim x As Integer
    Dim y As Integer
    x = 2
    y = 2
    Lastrow_A = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Row
    Set rng = Application.Range("Sheet2!A2:A" & Lastrow_A)
    Lastrow_C = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "C").End(xlUp).Row
    Set rng1 = Application.Range("Sheet1!A2:C" & Lastrow_C)
    'Debug.Print Sheet2.Cells(1, 2).Value
    For Each c1 In rng.Cells
        'Debug.Print c1.Value
        On Error Resume Next
        datavalue_Target = Application.WorksheetFunction.VLookup(c1, rng1.Cells, 2, False)
        If Err = 0 Then
        Sheets("Sheet2").Range("H" & x).Value = datavalue_Target
        End If
        On Error GoTo 0
        x = x + 1
    Next c1
    For Each c1 In rng.Cells
        'Debug.Print c1.Value
        On Error Resume Next
        datavalue_OuterB = Application.WorksheetFunction.VLookup(c1, rng1.Cells, 3, False)
        If Err = 0 Then
        Sheets("Sheet2").Range("I" & y).Value = datavalue_OuterB
        End If
        On Error GoTo 0
        y= y + 1
    Next c1