Excel多列INDEX MATCH公式VBA复现仅填充首列问题求助

需要复制14列数据，以下Excel数组公式手动拖拽填充时可忽略空值，运行正常：

=IF(INDEX(SSI!B:O, MATCH(CU11, SSI!A:A, 0), {1,2,3,4,5,6,7,8,9,10,11,12,13,14})="", "", INDEX(SSI!B:O, MATCH(CU11, SSI!A:A, 0), {1,2,3,4,5,6,7,8,9,10,11,12,13,14}))

但用VBA实现时仅首列被填充，原VBA代码如下：

Range("AU11").Select
ActiveCell.FormulaR1C1 = "=IF(INDEX(SSI!C[-45]:C[-32], MATCH(RC[52], SSI!C[-46], 0), {1,2,3,4,5,6,7,8,9,10,11,12,13,14})=""""", """"", INDEX(SSI!C[-45]:C[-32], MATCH(RC[52], SSI!C[-46], 0), {1,2,3,4,5,6,7,8,9,10,11,12,13,14}))"
Range("AU11").Select
Selection.AutoFill Destination:=Range("AU11:AU" & lr)

原VBA代码仅在AU11单元格写入公式后向下填充同一列，但原公式是数组公式，会返回14个结果对应14列（AU到BH列），仅操作单列自然只有首列有数据。

需要先将数组公式应用到横向14列的区域，再向下填充整个数据范围，或者直接给目标区域批量设置数组公式，效率更高。

修正后的VBA代码 #

' 定义目标起始单元格和数据最后一行
Dim targetStart As Range
Set targetStart = Range("AU11")
Dim lr As Long
lr = Cells(Rows.Count, "CU").End(xlUp).Row ' 假设CU列是匹配值列，可按需调整

' 给首行14列设置数组公式
With targetStart.Resize(1, 14)
    .FormulaArray = "=IF(INDEX(SSI!B:O, MATCH(CU11, SSI!A:A, 0), {1,2,3,4,5,6,7,8,9,10,11,12,13,14})="""", """", INDEX(SSI!B:O, MATCH(CU11, SSI!A:A, 0), {1,2,3,4,5,6,7,8,9,10,11,12,13,14}))"
End With

' 向下填充到最后一行
targetStart.Resize(lr - targetStart.Row + 1, 14).FillDown

代码说明 #

1. 定位起始单元格AU11，并通过CU列获取数据最后一行lr（可根据实际匹配值所在列调整）。
2. 使用Resize(1,14)选中首行14个单元格，设置数组公式，一次性填充首行14列的对应结果。
3. 用FillDown将首行公式向下填充至所有行，确保每一行的14列都返回正确数据。

更高效的批量设置方式 #

若不想分步操作，可直接给整个目标区域设置数组公式：

Dim targetRange As Range
Set targetRange = Range("AU11:BH" & lr) ' BH是AU列后第14列，可按需调整
targetRange.FormulaArray = "=IF(INDEX(SSI!B:O, MATCH(CU11:CU" & lr & ", SSI!A:A, 0), {1,2,3,4,5,6,7,8,9,10,11,12,13,14})="""", """", INDEX(SSI!B:O, MATCH(CU11:CU" & lr & ", SSI!A:A, 0), {1,2,3,4,5,6,7,8,9,10,11,12,13,14}))"

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