[R1C1] Formula with a loop index in VBA

[Steve Belsch]

Hi all,

I have created Do While loop over a range. My issue is that I want the "i" in this sum formula to be relative not absolute. Anyone have a suggestion?

Cells(i + 1, iCol).Formula = "=SUM(R" & j & "C:R" & i & "C)"


Here is the entire code:

Dim iCol As Integer
Dim i As Integer
Dim j As Integer


Application.ScreenUpdating = True

i = 3
j = i

'Sort the data so like data is grouped together.
Range("A5").CurrentRegion.Offset(1).Sort Range("A12"), 1

'Loops throught Col A 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 = 13 To 73 '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, 73)).Font.Bold = True
       i = i + 2
       j = i
    Else
       i = i + 1
    End If
Loop

Thanks

[SamT]

i is relative to what?

[p45cal]

Perhaps you mean change:

Cells(i + 1, iCol).Formula = "=SUM(R" & j & "C:R" & [i] & "C)"

to:

Cells(i + 1, iCol).Formula = "=SUM(R[" & j - i - 1 & "]C:R[-1]C)"

If that's the case then it would be faster to:

Dim iCol As Long
Dim i As Long
Dim j As Long

i = 3
j = i
'Sort the data so like data is grouped together.
Range("A5").CurrentRegion.Offset(1).Sort Range("A12"), 1
'Loops throught Col A 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
    Range(Cells(i + 1, 13), Cells(i + 1, 73)).FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
    Range(Cells(i + 1, 1), Cells(i + 1, 73)).Font.Bold = True
    i = i + 2
    j = i
  Else
    i = i + 1
  End If
Loop

Also you can do something very similar with the built-in Subtotal method:

Range("A5").CurrentRegion.Offset(1).Sort Range("A12"), 1
Range("A5").CurrentRegion.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(13, 14, 15 _
 , 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, _
 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, _
 68, 69, 70, 71, 72, 73), Replace:=True, PageBreaks:=False, SummaryBelowData:=True

or shorter still:

Range("A5").CurrentRegion.Offset(1).Sort Range("A12"), 1
Range("A5").CurrentRegion.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=[column(M:BU)], Replace:=True, PageBreaks:=False, SummaryBelowData:=True

[Steve Belsch]

To be more clear, here is what I am trying to accomplish.

At the end of the macro I would like to see this, as an example: Sum(BS$3:BS19) instead my formulation locks in on the row in the end of the formula like this, SUM(BS$3:BS$19). So, instead of giving me a relative (not locked row), it gives me an absolute (or locked row) at the end of the sum formula. Any other thoughts?

Thank you.

[Steve Belsch]

Hi. Thank you for your ideas. I will try to be more clear by what I mean as relative. In my Sum formula I am looking to get this as a result, I will use one example here: SUM(BS$3:BS19). The first row is locked (absolute) and the second row is not locked (relative). However, what I am getting instead is SUM(BS$3:BS$19). It is locking both rows. And it does this for every sum in the loop. Any thoughts?

Thank you.

[p45cal]

Cells(i + 1, iCol).FormulaR1C1 = "=SUM(R" & j & "C:R[-1]C)"

but faster to:

Sub ggggg2()
Dim iCol As Long
Dim i As Long
Dim j As Long

i = 3
j = i
'Sort the data so like data is grouped together.
Range("A5").CurrentRegion.Offset(1).Sort Range("A12"), 1
'Loops throught Col A 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
    Range(Cells(i + 1, 13), Cells(i + 1, 73)).FormulaR1C1 = "=SUM(R" & j & "C:R[-1]C)"
    Range(Cells(i + 1, 1), Cells(i + 1, 73)).Font.Bold = True
    i = i + 2
    j = i
  Else
    i = i + 1
  End If
Loop
End Sub

[Steve Belsch]

This code worked and solved my issue. Range(Cells(i + 1, 13), Cells(i + 1, 73)).FormulaR1C1 = "=SUM(R" & j & "C:R[-1]C)"

Thanks again for the code.

[snb]

Also to be written as:

c00="=SUM(R~C:R[-1]C)"
Cells(i + 1, 13).resize(,60)=replace(c00,"~",j)