3

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

ian0411
  • 4,115
  • 3
  • 25
  • 33
LukeJ
  • 31
  • 2
  • I have a feeling part of the reason is your use of `.Select`. Try to [avoid using `.Select/`.Activate`](https://stackoverflow.com/questions/10714251/) and see if that helps. – BruceWayne Sep 25 '17 at 21:28
  • Let me get this straight. The question here is: How to make a formula, that sums up all the cells above, stopping at first blank cell. Right? – kolcinx Sep 25 '17 at 21:29
  • 1
    @BruceWayne Agreed, but don't forget that he used the Macro-recorder. I think Excel teaches people more bad habits than anything else. Honestly, I think the only reason `.Select`, `.Activate`, `ActiveWorkbook`, etc. exists is to make recording macros possible. – K.Dᴀᴠɪs Sep 25 '17 at 21:37

2 Answers2

0
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.
kolcinx
  • 2,183
  • 1
  • 15
  • 38
  • Thanks Branislav. Is it possible to use End(xlUp) in some way within the original line of code - FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)" - to replace "-8" in this case, as it is the only variable, or does this not return a numeric value for the first cell in the range? – LukeJ Sep 26 '17 at 14:37
  • @LukeJ Hi. Yes you can! What you are doing, is giving the SUM function some range. The same is done in my code. If you can dynamicly determine the range by End(xlUp), then you can use its address to build the SUM argument. See my edit, comming soon. – kolcinx Sep 26 '17 at 15:43
  • Thanks again for your responses. It has been a huge help and guided me towards the answer I'm using which I added above - probably not the most elegant - but it works for all the situations I will encounter. – LukeJ Sep 27 '17 at 16:47
0

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
LukeJ
  • 31
  • 2