How to sum values in variable range in VBA?

Question

I have a table as shown below.

In column C I would like to Sum values from column A if they have the same index (column B). I would like to put sum result for all the rows if they have same index (as shown in column D).

Unfortunately the range of values with same index is variable and my macro can sum values just with 2 indexes. Can anyone help with it, please? Thanks!

enter image description here

Sub example()
Dim ws As Worksheet
Dim LastRow As Long
Dim n, i As Integer
Set ws = ActiveWorkbook.Sheets("Sheet2")

ws.Select

LastRow = Sheets("Sheet2").Range("A" & Sheets("Sheet2").Rows.Count).End(xlUp).Row

Range("C3:C" & LastRow).Select
Selection.ClearContents

For i = 3 To LastRow
If Range("B" & i + 1) - Range("B" & i) = 0 Then
Range("C" & i) = Range("A" & i + 1) + Range("A" & i)
Else
Range("C" & i) = Range("C" & i - 1)
End If
Next i


End Sub

Do you mean something like: "=SUMIFS(A:A;B:B;B3)" ? I would like to integrate it in macro, because its just a part from longer code — Ale
– Ale, Commented Mar 11, 2015 at 15:21

Rory · Accepted Answer · 2015-03-11 15:39:54Z

4

Here's one way:

Sub example()
    Dim ws                    As Worksheet
    Dim LastRow               As Long

    Set ws = ActiveWorkbook.Sheets("Sheet2")

    LastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

    With ws.Range("C3:C" & LastRow)
        .ClearContents
        .Value = ws.Evaluate("INDEX(SUMIF(B3:B" & LastRow & ",B3:B" & LastRow & ",A3:A" & LastRow & "),)")
    End With
End Sub

edited Mar 11, 2015 at 15:39

answered Mar 11, 2015 at 15:19

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ale Over a year ago

Thanks! In case i need to add some other conditions (to get kind of =SUMIFS), how to integrate it in this code?

Rory Over a year ago

It would be using SUMIFS and then similar range syntax for each range/condition pair.

Collectives™ on Stack Overflow

How to sum values in variable range in VBA?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related