excel - 仍然运行时错误 1004 '无法获取 WorksheetFunction 类的 VLookup 属性

Question

我收到此错误，并尝试使用许多解决方案进行修复，但我不知道为什么会发生此错误。你能帮我解决这个问题吗？在这段代码中，我两次使用了VLookup相同的函数源，只是工作表名称和列不同。另一个可以编译此代码并且没有任何错误。

我正在做，如果匹配工作表“ImportData2”的Q 列的VLookup显示结果，则工作表“ImportData2”的 B 列上的项目与工作表“Noallocate”的 A 列匹配VLookup

得到这个错误

源代码：

 'Vlook up function no import
    Dim Vrow1 As Long
    Dim myLookupValue1 As String
    Dim myFirstColumn1 As Long
    Dim myLastColumn1 As Long
    Dim myColumnIndex1 As Long
    Dim myFirstRow1 As Long
    Dim myLastRow1 As Long
    Dim myVLookupResult1 As String
    Dim myTableArray1 As Range
    For Vrow1 = 2 To 99999
        myLookupValue1 = Workbooks("ExpenseData.xlsm").Worksheets("ImportData2").Range("B" & Vrow).Value
        myFirstColumn1 = 1
        myLastColumn1 = 2
        myColumnIndex1 = 2
        myFirstRow1 = 2
        myLastRow1 = Workbooks("ExpenseData.xlsm").Worksheets("Noallocate").Range("b1").End(xlDown).Row

        With Workbooks("ExpenseData.xlsm").Worksheets("Noallocate")
            Set myTableArray1 = .Range(.Cells(myFirstRow1, myFirstColumn1), .Cells(myLastRow1, myLastColumn1))
        End With
      myVLookupResult1 = WorksheetFunction.VLookup(myLookupValue1, myTableArray1, myColumnIndex1, False)  'xxx
      Workbooks("ExpenseData.xlsm").Worksheets("ImportData").Range("Q" & Vrow).Value = myVLookupResult1 'xxx
   Next 'end function no import

标签： excelvba

Answer 1

请尝试下一个代码。在模块顶部放置“Option Explicit”将是一个好习惯。这将要求您声明所有变量。看起来，您的代码使用Vrow1但在您使用的循环内进行迭代Vrow......我只是在尝试使您的代码更友好之后才发现它。1用大变量名代替,是不好的2。这只会使代码更难通过查看来理解。尽可能简短，易于理解和调试：

Sub testVlookup()
 Dim Wb As Workbook, Vrow1 As Long, lastRDat As Long, wsNoall As Worksheet
 Dim wsImpD2 As Worksheet, myLookupValue1 As String, myLastRow1 As Long
 Dim myVLookupResult1 As String, myTableArray1 As Range

    Set Wb = Workbooks("ExpenseDataMcframe.xlsm")
    Set wsNoall = Wb.Worksheets("Noallocate")
    Set wsImpD2 = Wb.Worksheets("ImportData2")
    myLastRow1 = wsNoall.Range("b" & Rows.count).End(xlUp).Row
    lastRDat = wsImpD2.Range("B" & Rows.count).End(xlUp).Row
    For Vrow1 = 2 To lastRDat
        myLookupValue1 = wsImpD2.Range("B" & Vrow1).value

        Set myTableArray1 = wsNoall.Range(wsNoall.Cells(2, 1), wsNoall.Cells(myLastRow1, 2))

        On Error Resume Next 'for the case when lookup_value is not found
        myVLookupResult1 = WorksheetFunction.VLookup(myLookupValue1, myTableArray1, 2, False)  'xxx
        If Err.Number <> 0 Then
             Err.Clear: On Error GoTo 0
             Wb.Worksheets("ImportData").Range("Q" & Vrow1).value = "Not a mach"
        Else
            Wb.Worksheets("ImportData").Range("Q" & Vrow1).value = myVLookupResult1 'xxx
        End If
    On Error GoTo 0
   Next
End Sub

当没有找到lookup_value时，代码将返回（在Q：Q列）“不匹配”，您必须检查具体代码/无论它是什么......