Excel用VBA为指定单元格范围批量填充公式并支持动态调整行数

实现方案 #

方案1：纯手动操作，无需代码 #

• 先在起始单元格A27输入初始公式：

  =IF(ROWS($Q$27:Q27)<=$P$25,INDEX(DataTable[[#All],[Time]],$P27),"")
  

• 选中A27，向右拖拽填充柄到对应最后一列，完成首行多列填充，Excel会自动匹配DataTable的对应字段名
• 选中已填充的首行整行，向下拖拽填充柄到目标行数即可完成全部填充

方案2：VBA动态实现（支持按单元格配置调整填充行数） #

不需要逐单元格写赋值代码，几行代码即可完成，效率远高于逐单元格赋值：

Sub 批量填充公式()
    Dim ws As Worksheet
    Dim 填充行数 As Long
    ' 绑定工作表，可替换为指定表名，例如 Set ws = Sheets("业务表")
    Set ws = ActiveSheet
    ' 读取指定单元格的行数配置，可替换为你存放行数的单元格地址，例如 ws.Range("A1").Value
    填充行数 = ws.Range("P25").Value
    ' 直接给整个目标区域赋值初始公式，Excel会自动处理相对引用偏移，和手动拖拽效果完全一致
    ws.Range("A27").Resize(填充行数, 14).Formula = "=IF(ROWS($Q$27:Q27)<=$P$25,INDEX(DataTable[[#All],[Time]],$P27),"""")"
End Sub

代码说明：

• 代码中Resize(填充行数, 14)里的14对应A到N共14列，如果你实际需要填充15列，直接把14改成对应列数即可
• 填充行数直接读取指定单元格数值，修改单元格内容即可调整填充行数，无需修改代码
• 仅单次操作单元格，就算填充上万行也不会出现卡顿问题

内容的提问来源于stack exchange，提问作者Ahmed El-Shamy