Upon change in row value, Do a sumif one row back in another column

Question

I hope you can help me my VBA question. I would like to use a loop going down column A. Once a change is detected, I would like to insert a SUMIF formula in column C to total of column B if grouped the column A (Total offset to the right). A is already sorted. Kind of like Subtotal but without using the Subtotaling row.

A B C
1 2
1 6
1 3 11 =SUMIF(A:A,A3,B:B)

2 7
2 8 15 =SUMIF(A:A,A5,B:B)

3 8
3 6 14 =SUMIF(A:A,A7,B:B)

(without the added blank rows between 1 & 2 and 2 & 3 changes) I believe I am part of the way there with the following pieces of code pieces, but am having trouble getting to work together.

        Sub SUMIF_Upon_Change()
      Dim r As Long, mcol As String, i As Long


    ' find last used cell in Column A
      r = Cells(Rows.Count, "A").End(xlUp).Row

     ' get value of  last used cell in column A
      mcol = Cells(r, 1).Value

     ' insert rows by looping from bottom
      For i = r To 2 Step -1
         If Cells(i, 1).Value <> mcol Then
           Cells(n, 3).Formula = _
         "=SUMIF(A:A,RC[-1],B:B)"

    'AND / OR This added at the change row minus one row.

            'Places formula in each adjacent cell (in column "D").
            Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=SUMIF(A:A,A3,BB)"
         End If
      Next i

End Sub

Any help will be much appreciated.

Jim Simson Jim Simson · Accepted Answer · 2016-07-03T01:38:52

XLMatters, You pretty much have it. Your only major issue is that you seem to be mixing formula reference styles ("RC[-1]" and "A3"). You have to pick one or the other. Here is a working example of your code with a few minor modifications:

Sub SUMIF_Upon_Change()
    Dim r As Long, mcol As String, i As Long
    ' find last used cell in Column A
    r = Cells(Rows.Count, "A").End(xlUp).Row
    ' get value of  last used cell in column A
    mcol = Cells(r, 1).Value
        For i = r To 2 Step -1
            If Cells(i, 1).Value <> Cells(i + 1, 1).Value Then
                Cells(i, 3).Formula = "=SUMIF(A:A,A" & i & ",B:B)"
            End If
        Next i
End Sub

If your heart is set on FormulaR1C1 style, here you go:

Sub SUMIF_Upon_ChangeRC()
    Dim r As Long, mcol As String, i As Long
    ' find last used cell in Column A
    r = Cells(Rows.Count, "A").End(xlUp).Row
    ' get value of  last used cell in column A
    mcol = Cells(r, 1).Value
        For i = r To 2 Step -1
            If Cells(i, 1).Value <> Cells(i + 1, 1).Value Then
                Cells(i, 3).FormulaR1C1 = "=SUMIF(C1,RC1,C2)"
            End If
        Next i
End Sub

Should you have any other questions, just ask.

Upon change in row value, Do a sumif one row back in another column

1 Answers