如何用单公式填充多维数组列并批量复制到多列区域

需求说明 #

需要给多个指定列区域（例如J100:J106、K100:K106）批量填充公式，要求每一列的所有单元格都引用该列固定行（示例中为第103行）的单元格，最终效果如下：

此前单列的数组填充方案可行，但扩展到多列时，循环逻辑错误导致数组数据被覆盖，无法实现预期效果。

解决方案 #

核心思路 #

让二维数组的列数与目标列数完全对应，每一列对应一个目标列的公式模板，通过有序循环填充数组后，再将数组对应列写入目标区域，避免覆盖问题。

完整VBA代码 #

Option Explicit

Public Sub FillMultiColumnFormulasUsingArray()
    ' 基础参数定义
    Const MiddleRow As Long = 103       ' 固定引用的行号
    Const FirstRow As Long = 100        ' 目标区域起始行
    Const SecondRow As Long = 106       ' 目标区域结束行
    Dim TargetColumns As Variant
    TargetColumns = Array("J", "K")     ' 需要填充的目标列标识，可添加更多列如"J","K","L"
    
    ' 计算目标区域的行数
    Dim RowCount As Long
    RowCount = SecondRow - FirstRow + 1 ' 注意+1，因为包含首尾行
    
    ' 初始化二维数组：行数=目标区域行数，列数=目标列数
    Dim FormulaArray() As Variant
    ReDim FormulaArray(1 To RowCount, 1 To UBound(TargetColumns) + 1)
    
    ' 循环填充数组：外层遍历目标列，内层遍历行
    Dim ColIndex As Long
    Dim RowIndex As Long
    For ColIndex = LBound(TargetColumns) To UBound(TargetColumns)
        ' 给当前列的所有行赋值公式
        For RowIndex = 1 To RowCount
            FormulaArray(RowIndex, ColIndex + 1) = "=" & TargetColumns(ColIndex) & MiddleRow
        Next RowIndex
    Next ColIndex
    
    ' 方案1：一次性写入所有列区域（推荐，效率更高）
    Dim TargetRange As Range
    Set TargetRange = Worksheets("Model").Range(TargetColumns(0) & FirstRow & ":" & TargetColumns(UBound(TargetColumns)) & SecondRow)
    TargetRange.Formula = FormulaArray
    
    ' 方案2：按列逐个写入（适合需要单独处理某列的场景）
    ' For ColIndex = LBound(TargetColumns) To UBound(TargetColumns)
    '     Dim ColRange As Range
    '     Set ColRange = Worksheets("Model").Range(TargetColumns(ColIndex) & FirstRow & ":" & TargetColumns(ColIndex) & SecondRow)
    '     ' 提取数组的对应列写入
    '     ColRange.Formula = Application.Index(FormulaArray, 0, ColIndex + 1)
    ' Next ColIndex
End Sub

关键说明 #

• 数组维度匹配：初始化数组时，列数直接对应TargetColumns的元素数量，确保每一列都有独立的存储空间，避免覆盖。
• 循环顺序优化：外层循环遍历目标列，内层循环填充该列的所有行，确保每一列的公式只被赋值一次，不会被后续列的循环覆盖。
• 适配多列场景：只需在TargetColumns数组中添加更多列标识（如Array("J","K","L","M")），代码会自动适配，无需修改其他逻辑。
• 两种写入方式：
  • 方案1一次性写入所有区域，效率更高，适合批量处理。
  • 方案2按列单独写入，适合需要对某列做额外处理的场景。

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