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

dmadhup

Board Regular
Joined
Feb 21, 2018
Messages
146
Office Version
  1. 365
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
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]39[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
try this:
Code:
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
 
Upvote 0
Offthelip:
Thank you very much. I really like the approach - NEVER ACCESS THE WORKSHEET N A LOOP.

I APPRECIATE IT.
 
Upvote 0
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.

Code:
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
 
Upvote 0
this line of code:
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()
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top