vba - VLookUp 到另一个打开的工作簿错误

Question

我遇到了这种类型的错误，到目前为止还无法解决它。当我在 VBA 中使用 VLookUp 函数时，就会出现这种情况。

“Unable to get the VLookup property of the WorksheetFunction Class” error

这是我的完整代码:

Dim wbSLW as Workbook
Dim wbSLWDir as String

wbSLWDir = "C\Documents\test.xlsx" 'not constant directory
set wbSLW = Workbooks.Open(wbSLWDir)

Thisworkbook.Activate
With Thisworkbook.Sheets(1)
    ' line Error
    .Range("AE2").Formula = _
        WorksheetFunction.vlookUp(.Range("I2").Value, wbSLW.FullName & "Sheet3!E:F", 2, 0) 
End With

你能帮我解决一下吗？

Answer 1

Dim wbSLW As Workbook
Dim wbSLWDir As String

wbSLWDir = "C\Documents\test.xlsx" 'not constant directory
Set wbSLW = Workbooks.Open(wbSLWDir)

ThisWorkbook.Activate
With ThisWorkbook.Sheets(1)
    '''To get only the value
    .Range("AE2").Value = _
        WorksheetFunction.VLookup(.Range("I2").Value, wbSLW.Sheets("Sheet3").Range("E:F"), 2, 0)

    '''----OR-----
    '''To insert the formula

    ''' Syntax : .Formula = _
        "=VLOOKUP(Range-of-the-value-to-find" & _
                  ",'Path[FileName.xl*]SheetName'!Range-of-Array-To-Search" & _
                  ",Column-in-Array-to-output, FALSE)"

    '''With a Range reference using .Address (btw If you change 0 to 1, you'll have a $)
    .Range("AE2").Formula = _
        "=VLOOKUP(" & .Range("I2").Address(0,0) & ",'" & wbSLW.Path & "\[" & wbSLW.Name & "]" & "Sheet3'!E:F, 2, FALSE)"

    '''Or directly with address of the Range
    .Range("AE2").Formula = _
        "=VLOOKUP(I2,'" & wbSLW.Path & "\[" & wbSLW.Name & "]" & "Sheet3'!E:F, 2, FALSE)"
End With