动态VBA Autofill实现：起始行未知时填充公式至H40

问题核心 #

你碰到的「AutoFill method of Range class failed」错误，基本是因为源区域和目标区域维度不匹配，或是没准确定位到带公式的起始行。

实现步骤 #

1. 定位B列最后一个带公式的行：由于公式行是动态变化的，不能硬编码行号，用SpecialCells(xlCellTypeFormulas)精准抓取带公式的单元格，再取其中最大的行号。
2. 确定源范围：从B列公式行到H列公式行，这是要复制的公式区域。
3. 确定目标范围：从公式行的下一行开始到H40，确保源和目标的列数一致（都是B到H共7列），保证AutoFill可以正常执行。

完整可运行代码 #

Sub FillFormulasToH40()
    Dim ws As Worksheet
    Dim lastFormulaRow As Long
    Dim sourceRange As Range
    Dim targetRange As Range
    
    ' 指定目标工作表
    Set ws = ThisWorkbook.Worksheets("Result")
    
    On Error Resume Next ' 防止工作表无公式时触发错误
    ' 获取B列最后一个带公式的行号
    lastFormulaRow = ws.Range("B:B").SpecialCells(xlCellTypeFormulas).Rows(ws.Range("B:B").SpecialCells(xlCellTypeFormulas).Count).Row
    On Error GoTo 0
    
    ' 检查是否找到有效公式行，且公式行未超过40行
    If lastFormulaRow = 0 Or lastFormulaRow >= 40 Then
        MsgBox "未找到公式行，或公式行已达/超过40行，无需填充"
        Exit Sub
    End If
    
    ' 定义源公式区域（B到H列的公式行）
    Set sourceRange = ws.Range(ws.Cells(lastFormulaRow, "B"), ws.Cells(lastFormulaRow, "H"))
    ' 定义目标填充区域（从公式行到H40的完整区域）
    Set targetRange = ws.Range(ws.Cells(lastFormulaRow, "B"), ws.Cells(40, "H"))
    
    ' 执行自动填充，复制公式格式
    sourceRange.AutoFill Destination:=targetRange, Type:=xlFillDefault
End Sub

关键细节说明 #

• SpecialCells(xlCellTypeFormulas)：精准筛选带公式的单元格，避免误选普通数据行。
• 错误处理：加入On Error Resume Next处理无公式的场景，后续通过行号判断是否继续执行。
• 维度匹配：源区域是1行7列，目标区域是同列数的多行区域，维度一致才不会触发AutoFill报错。
• 前置校验：如果公式行已经≥40，直接退出程序，避免无效操作。

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