CODEWITHSUNDEEP
vbaexcel
Ale
Ale

Reputation: 665

How to sum values in variable range in VBA?

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

Upvotes: 2

Views: 9019

Answers (1)

Rory
Rory

Reputation: 34075

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

Upvotes: 4

Related Questions