Question

我有一个包含不同大小的多张工作簿。我想在最后一行后添加一个总列, 并在所有列中复制公式。我定义了最后一行和列, 公式在正确的地方显示为预期值, 但是在试图填充时会收到错误。我如何正确引用两个动态单元格来填充? 我只是使用一个单页来测试, 最终将会在书中的所有工作表中循环。

Sub Addtotals()

    Dim Bord As Worksheet
    Dim LRow As Long
    Dim LCol As Long
    Dim frmcell As Range

    Set Bord = Sheets("Borders")
    With Bord
     --> Define last rows and columns
        LRow = .Range("A" & Rows.Count).End(xlUp).Row
        LCol = .Range("A" & Columns.Count).End(xlToLeft).Column

     --> Add Total text to first column
        .Range("A" & LRow).Offset(1, 0).Select
        ActiveCell = "Total"

     --> Add formula to next column
        Set frmcell = Range("B" & LRow + 1)
        frmcell.Formula = "=sum(B2:B" & LRow & ")"

     --> Fill formula across range
        frmcell.Select
        Selection.AutoFill Destination:=Range(frmcell & LCol), Type:=xlFillDefault
    End With
End Sub

谢谢:)

Answer 1

像这样?

Option Explicit

Sub Addtotals()
    Dim Bord As Worksheet
    Dim LRow As Long, LCol As Long

    Set Bord = Sheets("Borders")
    With Bord
     --> Define last rows and columns
        LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
        LCol = .Cells(1, Columns.Count).End(xlToLeft).Column

     --> Add Total text to first column
        .Range("A" & LRow).Value = "Total"

     --> Fill formula across range
        .Range("B" & LRow & ":" & _
        Split(Cells(, LCol).Address, "$")(1) & LRow).Formula = _
        "=Sum(B2:B" & LRow - 1 & ")"
    End With
End Sub

友情链接