VBA中WorksheetFunction.Lookup与Choose组合使用报错求助

错误根源 #

1. 变量类型错误：Y被定义为Long整数类型，赋值22.5时会自动截断成22，导致查找值和实际目标不符。
2. 参数格式不兼容：WorksheetFunction.Choose搭配Range.Value返回的二维数组，无法被WorksheetFunction.Lookup正确识别。
3. 冗余激活操作：手动激活窗口和工作表属于多余操作，还可能引发对象引用错误。
4. 结果变量类型不当：如果目标单元格是小数，用Long接收会丢失精度。

修正代码 #

Sub Mysub()
    Dim X As Double
    Dim Y As Double
    Dim Z As Integer
    Dim ws As Worksheet
    
    Y = 22.5
    Z = 3
    
    ' 直接绑定目标工作表，避免激活操作
    Set ws = Workbooks("My file.xlsm").Worksheets("My sheet")
    
    ' 先通过Choose选定目标列，再执行Lookup查找
    X = WorksheetFunction.Lookup(Y, ws.Range("A1:A201"), ws.Choose(Z, ws.Range("C1:C201"), ws.Range("B1:B201"), ws.Range("D1:D201")))
End Sub

进阶优化建议 #

• 改用Application.Lookup：和WorksheetFunction版本不同，它找不到匹配值时不会直接抛错，而是返回错误值，方便后续判断处理：

Sub Mysub()
    Dim X As Variant
    Dim Y As Double
    Dim Z As Integer
    Dim ws As Worksheet
    
    Y = 22.5
    Z = 3
    
    Set ws = Workbooks("My file.xlsm").Worksheets("My sheet")
    
    X = Application.Lookup(Y, ws.Range("A1:A201"), ws.Choose(Z, ws.Range("C1:C201"), ws.Range("B1:B201"), ws.Range("D1:D201")))
    
    ' 判断是否找到有效结果
    If IsError(X) Then
        MsgBox "未找到匹配的22.5对应数据"
    Else
        MsgBox "查找结果：" & X
    End If
End Sub

• 确认查找区域排序：LOOKUP要求A列数据是升序排列的，如果数据无序，建议改用VLOOKUP(Y, ws.Range("A:D"), Z+2, False)（这里Z=3对应D列，即第4列，Z+2=4）来做精确匹配。

内容的提问来源于stack exchange，提问作者VBA_trainee