vba - 如何编辑求和公式以选择动态单元格范围？

Question

我录制了一个宏，确保选择了“使用相对引用”，但运行宏时，求和函数始终选择将显示总计的单元格上方的 8 个单元格，即使我使用 Ctrl+Shift+向上箭头 选择 正上方的所有非空白单元格:

ActiveCell.FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)

我查看了以下内容，这些内容与我想要实现的目标类似，但我的是相反的，并且无法弄清楚如何修改我的代码，它将对列中向上移动的每个单元格进行求和，直到它命中一个空白单元格。

• Select Cells in Range until row is blank

• Select a dynamic range of cells

目标是能够在工作表中的不同点输入小计，对其中不同单元格数量的范围进行求和。

如果有助于查看上下文，这就是整个宏的样子:

Sub InsertTotal()
'
' InsertTotal Macro
' Insert blank rows, bold line and total amount
'
' Keyboard Shortcut: Ctrl+y
'
    ActiveCell.Rows("1:2").EntireRow.Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Offset(0, 7).Range("A1").Select
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)"
    ActiveCell.Offset(-1, -7).Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveCell.Select
End Sub

任何意见或建议都会有很大的帮助

Answer 1

Public Function sumAbove(ByVal CellSelected As Range) As Double
    Dim FirstSelected As Range
    Dim TopMostNonBlankCell As Range

    Set FirstSelected = CellSelected.Cells(1) 'if user inputs more than one cell, we use only the first

    If FirstSelected.Offset(-1).Value2 = vbNullString Then 'we check, if the value above FirstSelected is blank
        Set TopMostNonBlankCell = FirstSelected
    Else 'If it is not blank, we use CTRL+SHIFT+UP
        Set TopMostNonBlankCell = FirstSelected.End(xlUp)
    End If
    'Some error handling should be put above, to handle if the sumAbove is used in first row.

    sumAbove = WorksheetFunction.Sum(Range(TopMostNonBlankCell, CellSelected).Value2)
End Function

编辑1

要将其合并到您的代码中，请尝试使用:
ActiveCell.FormulaR1C1 = "=SUM("& ActiveCell.Offset(-1).Address(ReferenceStyle:=xlR1C1) & ":"& ActiveCell.Offset(-1).End(xlUp).Address(ReferenceStyle: =xlR1C1) & ")"

解释一下:

• 为了避免循环引用，我们需要将事件单元格偏移 -1 行。
• 您可以简化代码以使用默认的 A1 引用符号，如下所示:ActiveCell.Formula = "=SUM("& ActiveCell.Offset(-1).Address & ":"& ActiveCell.Offset(-1) ).End(xlUp).地址 & ")"。结果是一样的。
• 您将需要一些异常处理(请参阅上面的我的函数)。
• 为了使代码更加高效和可读，可以使用一些范围变量，例如 Set ActiveCellMinusRow = ActiveCell.Offset(-1) 并在我建议的代码中使用它。