Excel 使用宏复制公式

Question

我正在尝试让 Excel 自动执行某些任务，例如。将A1中的公式从A2复制到A5000，然后将D1中的公式从D2复制到D5000

如果只是 2 个不同的列，我将只是复制和粘贴，但它实际上是针对同一选项卡中的 60 列，并且这些公式列彼此不相邻。

有什么建议可以加快速度吗？非常感激!

谢谢

Answer 1

将列索引号分配给数组并循环遍历它们。

Sub extendFormulas()
    Dim c As Long, fr As Long, lr As Long, arrCols As Variant

    arrCols = Array(1, 4)       'assign a collection of the column index numbers; this is A and D
    fr = 1                      'assign a starting row that already has the formula
    lr = 5000                   'assign a finishing row
    With Worksheets("Sheet1")
        For c = LBound(arrCols) To UBound(arrCols)
            .Range(.Cells(fr, arrCols(c)), .Cells(lr, arrCols(c))).Formula = _
                .Cells(fr, arrCols(c)).Formula
        Next c
    End With
End Sub