PDA

View Full Version : [SOLVED] [R1C1] Formula with a loop index in VBA



Steve Belsch
11-22-2019, 12:44 PM
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
11-22-2019, 04:15 PM
i is relative to what?

p45cal
11-23-2019, 05:00 PM
Perhaps you mean change:

Cells(i + 1, iCol).Formula = "=SUM(R" & j & "C:R" & & "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 [I]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
11-25-2019, 09:04 AM
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
11-25-2019, 09:07 AM
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
11-25-2019, 10:07 AM
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
11-25-2019, 01:40 PM
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
11-26-2019, 01:20 AM
Also to be written as:


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