Excel使用宏复制公式

Question

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

如果只是两个不同的列，我只是复制和粘贴，但它实际上是同一选项卡中的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