vba - Excel 2016 VBA 中的 Vlookup 函数

Question

我需要一些有关 Excel 的 Vlookup 函数的帮助。我有 2 张工作表。第一个有公司代码，第二个我需要根据公司代码查找值。相关代码为:

Dim Compcode, AUC, OB As String
Dim WS1, WS2 As Worksheet
Set WS1 = ThisWorkbook.Worksheets("Main Sheet")
Set WS2 = ThisWorkbook.Worksheets("Data")
Compcode = WS1.Cells(2, 1).Value 'Company code WS1 A2

AUC = Application.WorksheetFunction.VLookup(Compcode, WS2.Range("A2:C30"), 2, False)
OB = Application.WorksheetFunction.VLookup(Compcode, WS2.Range("A2:C30"), 3, False)

我尝试修改每个变量的 Dim 和 Vlookup 函数本身，但我总是收到相同的错误消息:

运行时错误“438”:对象不支持此属性或方法

Answer 1

WorksheetFunction.Vlookup 对于输入来说非常不稳定，并且很容易引发运行时错误，正如您遇到的那样。

您的问题可能是多种问题之一，但我建议的解决方案是仅使用 Find 来代替:

Sub test()
    Dim Compcode As Variant, AUC As Variant, OB As Variant
    Dim WS1 As Worksheet, WS2 As Worksheet

    Set WS1 = ThisWorkbook.Worksheets("Main Sheet")
    Set WS2 = ThisWorkbook.Worksheets("Data")
    Compcode = WS1.Cells(2, 1).Value 'Company code WS1 A2

    Dim rng As Range
    Set rng = WS2.Range("A2:C30").Find(Compcode, , , xlWhole)

    If Not rng Is Nothing Then
        AUC = rng.Offset(0, 1).Value
        OB = rng.Offset(0, 2).Value
    End If
End Sub