VBA多条件XLookup运行时错误1004问题求助

错误原因拆解 #

• Range引用语法错误：shLookup.Range("a", "b" & i) 和 shData.Range("b" & "c") 不符合VBA的Range引用规则，前者需指定连续单元格区域，后者是固定列区域而非错误的字符串拼接。
• 多条件XLookup参数格式错误：工作表函数的多条件查找需要将多个条件包装为数组，而非直接传入Range对象。
• 数据类型风险：Integer 无法容纳Excel的最大行数（超过32767行时会溢出），应改用Long类型。

修正后的代码 #

Private Sub CommandButton2_Click()
    Dim shData As Worksheet
    Dim shLookup As Worksheet
    Dim i As Long
    Dim lastRow As Long
    
    Set shData = ThisWorkbook.Worksheets("Data")
    Set shLookup = ThisWorkbook.Worksheets("Lookup")
    
    lastRow = shLookup.Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 1 To lastRow
        ' 多条件查找：将A、B列当前行的值作为数组传入XLookup
        shLookup.Range("C" & i).Value = WorksheetFunction.XLookup( _
            Array(shLookup.Range("A" & i).Value, shLookup.Range("B" & i).Value), _
            Array(shData.Range("B:B").Value, shData.Range("C:C").Value), _
            shData.Range("D:D").Value, _
            "-" _
        )
    Next i
End Sub

补充优化说明 #

如果处理大表格，循环会影响效率，可直接批量写入数组公式后转换为值：

' 批量写入公式（无需循环）
shLookup.Range("C1:C" & lastRow).Formula2 = _
    "=XLOOKUP((A1:B1),(Data!B:B,Data!C:C),Data!D:D,""-"")"
' 可选：将公式结果转换为静态值
shLookup.Range("C1:C" & lastRow).Value = shLookup.Range("C1:C" & lastRow).Value

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