sum the consecutive

[ibrahimo00o]

sum data in same Column of consecutive positive and negative in excel

like this

2
6
-2
1
3
-5
-5
-1
9

i need to transform it to

8
-2
4
-11
9

note that may be found more than 3 values consecutive positive or negative

 

[Domenic]

Assuming that Column A, starting A2, contains the data, try...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] Cell [color=darkblue]As[/color] Range
    
    Application.ScreenUpdating = [color=darkblue]False[/color]

    [color=darkblue]Set[/color] Cell = Range("A2")
    
    [color=darkblue]Do[/color] [color=darkblue]While[/color] Cell.Value <> ""
        [color=darkblue]If[/color] Sgn(Cell) = Sgn(Cell.Offset(1, 0)) [color=darkblue]Then[/color]
            Cell.Value = Cell.Value + Cell.Offset(1, 0).Value
            Cell.Offset(1, 0).EntireRow.Delete
        [color=darkblue]Else[/color]
            [color=darkblue]Set[/color] Cell = Cell.Offset(1, 0)
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Loop[/color]
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[pgc01]

Hi

Are zeroes allowed? In that case what should be the result of:

2
0
3
1
0
0
-5
-1
3
0

 

[Domenic]

ibrahimo00o,

Actually, there's a flaw in my code. Try the following instead..

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    [color=darkblue]For[/color] i = LastRow [color=darkblue]To[/color] 2 [color=darkblue]Step[/color] -1
        [color=darkblue]If[/color] Sgn(Cells(i, "A")) = Sgn(Cells(i - 1, "A")) [color=darkblue]Then[/color]
            Cells(i - 1, "A").Value = Cells(i - 1, "A").Value + Cells(i, "A").Value
            Rows(i).Delete
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

pgc01,

Good point... Based on your example, my macro will return the following results...


2
0
4
0
-6
3
0

If this is not the desired result, the code will need to be amended.

 

[ibrahimo00o]

ibrahimo00o,

Actually, there's a flaw in my code. Try the following instead..

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    [color=darkblue]For[/color] i = LastRow [color=darkblue]To[/color] 2 [color=darkblue]Step[/color] -1
        [color=darkblue]If[/color] Sgn(Cells(i, "A")) = Sgn(Cells(i - 1, "A")) [color=darkblue]Then[/color]
            Cells(i - 1, "A").Value = Cells(i - 1, "A").Value + Cells(i, "A").Value
            Rows(i).Delete
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

pgc01,

Good point... Based on your example, my macro will return the following results...


2
0
4
0
-6
3
0

If this is not the desired result, the code will need to be amended.

its work.... great thank u very much

i was tired to solve this prob.........

but now every things okay................. thx

 

[Domenic]

You're very welcome! Thanks for the feedback!

 

[ibrahimo00o]

I NEED TO COUNT consecutive positive or negative VALUES IN SECOND Column IN THE SAME CODE IN THE ABOVE LIKE THIS EXAMPLE

2
6
-2
1
3
-5
-5
-1
9

i need to transform it to



C ___________D

8____________2
-2___________1
4____________2
-11__________3
9____________1

 

[ibrahimo00o]

anyone can help !

 

[ibrahimo00o]

up

 

[ibrahimo00o]

anyone help !