VBA按钮批量写入XLOOKUP公式报错，求代码优化方案

问题描述 #

希望通过带按钮的VBA代码在多个单元格中写入XLOOKUP公式，但运行以下代码时报错：

Sub RSPK()
    
    Range("E18").Select
    ' Selecting Minta column
    ActiveCell.Formula = "=XLOOKUP(B18;'DISTRIBUSI OKTOBER 2024'!B15:B1030;'DISTRIBUSI OKTOBER 2024'!Q15:Q1030;0;0)"
    
    Range("E19").Select
    ' Selecting Kirim column
    ActiveCell.Formula = "=XLOOKUP(B18;'DISTRIBUSI OKTOBER 2024'!B15:B1030;'DISTRIBUSI OKTOBER 2024'!R15:R1030;0;0)"
    
    Range("E20").Select
    ' Selecting ED column
    ActiveCell.Formula = "=XLOOKUP(B18;'DISTRIBUSI OKTOBER 2024'!B15:B1030;'DISTRIBUSI OKTOBER 2024'!S15:S1030;0;0)"
    
    Range("E21").Select
    ' Selecting HARGA column
    ActiveCell.Formula = "=XLOOKUP(B18;'DISTRIBUSI OKTOBER 2024'!B15:B1030;'DISTRIBUSI OKTOBER 2024'!U15:U1030;0;0)"
    
    Range("E22").Select
    ' Selecting KET column
    ActiveCell.Formula = "=XLOOKUP(B18;'DISTRIBUSI OKTOBER 2024'!B15:B1030;'DISTRIBUSI OKTOBER 2024'!T15:T1030;0;0)"

End Sub

错误原因分析 #

1. 公式参数分隔符不匹配：VBA的Formula属性要求使用英文逗号,作为参数分隔符，代码中用了分号;（仅适配本地化Excel的分隔规则），导致公式解析失败。
2. 冗余的单元格选择操作：Select和ActiveCell不仅降低代码效率，还可能因工作表切换触发引用错误。
3. 代码冗余：重复编写XLOOKUP公式，仅返回列不同，可简化逻辑提升可维护性。

优化后的代码 #

方案一：适配英文Excel（使用Formula属性） #

Sub RSPK()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet ' 可替换为具体工作表名，如Worksheets("你的工作表")
    
    ' 直接为单元格赋值公式，避免Select操作
    ws.Range("E18").Formula = "=XLOOKUP(B18,'DISTRIBUSI OKTOBER 2024'!B15:B1030,'DISTRIBUSI OKTOBER 2024'!Q15:Q1030,0,0)"
    ws.Range("E19").Formula = "=XLOOKUP(B18,'DISTRIBUSI OKTOBER 2024'!B15:B1030,'DISTRIBUSI OKTOBER 2024'!R15:R1030,0,0)"
    ws.Range("E20").Formula = "=XLOOKUP(B18,'DISTRIBUSI OKTOBER 2024'!B15:B1030,'DISTRIBUSI OKTOBER 2024'!S15:S1030,0,0)"
    ws.Range("E21").Formula = "=XLOOKUP(B18,'DISTRIBUSI OKTOBER 2024'!B15:B1030,'DISTRIBUSI OKTOBER 2024'!U15:U1030,0,0)"
    ws.Range("E22").Formula = "=XLOOKUP(B18,'DISTRIBUSI OKTOBER 2024'!B15:B1030,'DISTRIBUSI OKTOBER 2024'!T15:T1030,0,0)"
End Sub

方案二：适配本地化Excel（使用FormulaLocal属性） #

如果你的Excel默认用分号;作为参数分隔符，改用FormulaLocal属性：

Sub RSPK()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
    
    ws.Range("E18").FormulaLocal = "=XLOOKUP(B18;'DISTRIBUSI OKTOBER 2024'!B15:B1030;'DISTRIBUSI OKTOBER 2024'!Q15:Q1030;0;0)"
    ws.Range("E19").FormulaLocal = "=XLOOKUP(B18;'DISTRIBUSI OKTOBER 2024'!B15:B1030;'DISTRIBUSI OKTOBER 2024'!R15:R1030;0;0)"
    ws.Range("E20").FormulaLocal = "=XLOOKUP(B18;'DISTRIBUSI OKTOBER 2024'!B15:B1030;'DISTRIBUSI OKTOBER 2024'!S15:S1030;0;0)"
    ws.Range("E21").FormulaLocal = "=XLOOKUP(B18;'DISTRIBUSI OKTOBER 2024'!B15:B1030;'DISTRIBUSI OKTOBER 2024'!U15:U1030;0;0)"
    ws.Range("E22").FormulaLocal = "=XLOOKUP(B18;'DISTRIBUSI OKTOBER 2024'!B15:B1030;'DISTRIBUSI OKTOBER 2024'!T15:T1030;0;0)"
End Sub

进阶优化：减少代码冗余 #

通过循环批量赋值，后续修改返回列只需调整数组即可：

Sub RSPK()
    Dim ws As Worksheet
    Dim targetCells As Variant
    Dim returnColumns As Variant
    Dim i As Integer
    
    Set ws = ThisWorkbook.ActiveSheet
    targetCells = Array("E18", "E19", "E20", "E21", "E22")
    returnColumns = Array("Q", "R", "S", "U", "T") ' 对应各单元格的返回列
    
    For i = LBound(targetCells) To UBound(targetCells)
        ws.Range(targetCells(i)).Formula = _
            "=XLOOKUP(B18,'DISTRIBUSI OKTOBER 2024'!B15:B1030,'DISTRIBUSI OKTOBER 2024'!" & returnColumns(i) & "15:" & returnColumns(i) & "1030,0,0)"
    Next i
End Sub

额外注意事项 #

• 确保工作表DISTRIBUSI OKTOBER 2024存在于当前工作簿中，否则会触发引用错误。
• 若要固定查找区域（避免下拉时区域偏移），可添加绝对引用符号$，例如'DISTRIBUSI OKTOBER 2024'!$B$15:$B$1030。

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