How to sum the column value based on other column value?

[dmadhup]

Hi,

I have following data and how to write VBA to get 39 and 22 and so on:
Once we see 1 in Column A. Strats adding next number in column B until next 1 in column 1.

Exanple: 39 = 5+5+6+7+3+8+3+2
22 = 9+6+3+4

Col-A Col-B

1	39
2	5
3	5
4	6
5	7
6	3
7	8
8	3
9	2
1	22
2	9
3	6
4	3
5	4
1

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[offthelip]

try this:

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
sumr = 0
For i = lastrow To 1 Step -1
 If inarr(i, 1) = 1 Then
  Cells(i, 2) = sumr
  sumr = 0
 Else
  sumr = sumr + inarr(i, 2)
 End If
Next i


End Sub

 

[dmadhup]

Offthelip:
Thank you very much. I really like the approach - NEVER ACCESS THE WORKSHEET N A LOOP.

I APPRECIATE IT.

 

[dmadhup]

I am modifying above code if I have more columns like Column B, Column C and many more. In future, I may have many rows and may columns

But it gives me subscript out of error.

Sub test()
lastrow = Cells(Rows.count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
sumr = 0
sumr2 = 0
For i = lastrow To 1 Step -1
 If inarr(i, 1) = 1 Then
  Cells(i, 2) = sumr
  Cells(i, 3) = sumr2
  sumr = 0
  sumr2 = 0
 Else
  sumr = sumr + inarr(i, 2)
  sumr2 = sumr2 + inarr(i, 3)
 End If
Next i

End Sub

 

[offthelip]

this line of code:

inarr = Range(Cells(1, 1), Cells(lastrow, 2))

Loads a defined range into the variant array, the range I defined was from cells (1,1) which is A1 to (cells(lastrow,2) which the Lastrow of column B
If you want to use more columns you need to change the Cells(lastrow,2) to cells(lastrow, N) where N is the number of the lastrow that you want to use.
To find the column number of a column in excel there is a very useful function that I use all the time; put the following function in to any cell in the column you want to find the numberr of and it will give the number:

=Column()

 

[dmadhup]

Thank you very much.