excel - VBA自动填充两列的最后一行

Question

我想自动填充第 4 列和第 5 列中的公式，假设我不知道哪一行包含最后一条数据。

**意识到我捏造了这个问题，做了一点修改。TIA

标签： excelvba

Answer 1

两个部分：

1. 获取对要复制的公式的引用和要填充的区域
2. 复制公式

查看内联注释

Sub Demo()
    Dim rFormulaToCopy As Range
    Dim FormulaColumn As Long, NumOfFormula As Long
    Dim DataColumn As Long
    
    '~~~ update to match your sheet layout
    FormulaColumn = 4
    NumOfFormula = 2
    DataColumn = 1
    
    With ActiveSheet '~~~ specify the required sheet
        ' Reference the last row of Formula
        Set rFormulaToCopy = .Cells(.Rows.Count, FormulaColumn).End(xlUp).Resize(, NumOfFormula)
        ' Reference the missing formula range, including the last Formula row.
        ' Assign from/to the Formula property to copy formulas (Excel will update relative references)
        rFormulaToCopy.Resize(.Cells(.Rows.Count, DataColumn).End(xlUp).Row - rFormulaToCopy.Row + 1).Formula = rFormulaToCopy.Formula
    End With
End Sub