Understanding a sum formula as a subtotal within a for loop

Question

I found a Code which creates a subtotal within a table. The Formula works fine, but I do not understand the Syntax of the sum Formula for the subtotal, which is:

"=SUM(R" & j & "C:R" & i & "C)"

What is meant with R, C:R and C? Can anybody please translate how the respective Output, for example =SUMME(E$4:E$4) corresponds to this formula?

This is the subtotal output Excel function:

The Code is as follows:

Dim iCol As Integer 
Dim i As Integer 'Makro f?ngt ab diese Zeilenummer an
Dim j As Integer 'Makro geht mit diese Zeilenummer im Loop weiter

Worksheets("Italy").Activate

Application.ScreenUpdating = False
i = 4 'Makro f?ngt ab diese Zeilenummer ab
j = i

'Loops throught Col B Checking for match then when there is no match add Sum
Do While Range("A" & i) <> ""
    If Range("A" & i) <> Range("A" & (i + 1)) Then
        Rows(i + 1).Insert
        Range("A" & (i + 1)) = "Subtotal " & Range("A" & i).Value
        For iCol = 5 To 11 'Columns to Sum
            Cells(i + 1, iCol).Formula = "=SUM(R" & j & "C:R" & i & "C)"
        Next iCol
        Range(Cells(i + 1, 1), Cells(i + 1, 10)).Font.Bold = True
        Range(Cells(i + 1, 1), Cells(i + 1, 10)).Interior.Color = RGB(221, 237, 245)
        i = i + 2
        j = i
    Else
        i = i + 1
    End If
Loop
Application.ScreenUpdating = True

Note that row/column counting variables should be of type `Long` Excel has more rows than `Integer` can handle. — Pᴇʜ, May 15 '19 at 07:32
You have a large number of range objects (Range, Cells, Rows) which do not specify a wb & ws. This is bound to raise errors sooner than later. Also, read on [how to avoid Activate and Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Tim Stack, May 15 '19 at 07:33
`R = row` `C = Column` `R6 = Row 6` `RC1 = Row where you are inserting the formula and column 1`. Thought there is a builtin formula on excel called `SUBTOTAL` that allows you to do that. — Damian, May 15 '19 at 07:40

score 1 · Accepted Answer · edited May 15 '19 at 07:58

Hope I did understood what you are trying to ask. First of all you are initializing two variables, in your case i and j that are going to help you iterate through the cells.

i and j will replace in the sum formula the first, second, third and so on row and column. So, instead of =SUM(R" & j & "C:R" & i & "C) you will have =SUM(R1 & "C:R" & 4C).

The difference in between A1 and R1C1 is the way you look at it and reference to it. Using R1C1 notation can help you to iterate easier through the cells. Going to the next cell (to the right) to A1 will be something like: R1C2.

Understanding a sum formula as a subtotal within a for loop

1 Answers1