How do I edit my sum formula to select a dynamic range of cells?

user_8672517 0
Viewed 1309

I recorded a macro making sure Use Relative References was selected but when running the macro, the sum function always selects the 8 cells above the cell where the total will appear, even though I recorded it using Ctrl+Shift+Arrow Up to select all non-blank cells directly above How the formula was entered:

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

I've looked at the followings which are similar to what I want to achieve, but mine is in reverse, and can't figure out how to amend my code where it will sum every cell moving up the column until it hits a blank cell.

The objective is to be able to enter subtotals at different points in the worksheet where they sum ranges with differing numbers of cells in them.

This is what the entire macro looks like if it would help to see context:

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

Any comments or suggestions will be a huge help

excel-formula vba excel
2 Answers
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

EDIT1

To incorporate this into your code, try to use this:
ActiveCell.FormulaR1C1 = "=SUM(" & ActiveCell.Offset(-1).Address(ReferenceStyle:=xlR1C1) & ":" & ActiveCell.Offset(-1).End(xlUp).Address(ReferenceStyle:=xlR1C1) & ")"

To explain:

  • To avoid circular reference, we need to offset the active cell by -1 row.
  • You can simplify the code to use default A1 reference notation like this: ActiveCell.Formula = "=SUM(" & ActiveCell.Offset(-1).Address & ":" & ActiveCell.Offset(-1).End(xlUp).Address & ")". The result is the same.
  • You will need some exception handling (see my function above).
  • To make the code slightly more efficient and readable, some range variable like Set ActiveCellMinusRow = ActiveCell.Offset(-1) and use it in the code I proposed.
user_46368010

This is the solution I used as it covers off all the options I can think of in the data sets it will be used on.

If ActiveCell.Offset(-2) = "" Then
    ActiveCell.Formula = "=SUM(" & ActiveCell.Offset(-1).Address & ":" & ActiveCell.Offset(-1).Address & ")"
Else
    ActiveCell.Formula = "=SUM(" & ActiveCell.Offset(-1).Address & ":" & ActiveCell.Offset(-1).End(xlUp).Address & ")"
End If
user_86725170
Related