# HELP! Almost got it..Summing numbers between

1. ## HELP! Almost got it..Summing numbers between

I'm trying to sum numbers between non zero's but when there are multiples in rows it doesn't work. For example row 5 should sum to 6 and row 6 should sum to 12(1+8+3). However with the code I have below rows 5 and 6 are both summing to 16 (4+1+8+3). Please help!!

Thanks
Trevor
Eg.
1 0 3
2 Yes 2
3 0 5
4 0 2
5 Yes 6
6 Yes 4
7 0 1
8 0 8
9 0 3
10 Yes 2

Sub SumBetween()
' This macro sums the differences between Yes's
' Macro1 Macro
' Macro recorded 22-08-2005

'Clears column V, sum of the differences
Sheets("WorkPage").Select
Columns("V:V").Select
Selection.ClearContents

'Sets ranges to column P then formats to formula and text cell
Set rng = Columns(16).SpecialCells(xlFormulas, xlTextValues)
i = 0
For Each ar In rng.Areas
i = i + 1
If i <> 1 Then
Set rng1 = Range(cell.Offset(1, 4), ar.Offset(-1, 4))
cell.Offset(0, 6).Value = Abs(Application.Sum(rng1))
End If
Set cell = ar
Next
MinPower
Calculate
End Sub  Register To Reply

2. ## Re: HELP! Almost got it..Summing numbers between

The following code gets results of 6 & 16 for rows 5 & 6. I don't understand
the logic behind your desired results of 6 & 12. (0 & 12 I could understand,
6 & 16 I can understand, but not 6 & 12).

My Data is in columns B & C (obviously) and sums are displayed in D. Adjust

Dim rng As Range
Dim ar As Range
Dim Cell1 As Range
Dim Cell2 As Range
Dim rngSum As Range
Dim i As Long

Set rng = ActiveSheet.Range("B1:C10")

i = 1
For Each ar In rng
If InStr(1, rng.Cells(i, 1), "Yes") > 0 Then
Set Cell1 = rng.Cells(i, 2)
Do Until Cell1 Is Nothing
If InStr(1, rng.Cells(i + 1, 1), "Yes") > 0 Or rng.Cells(i +
1, 1) = "" Then
Set Cell2 = rng.Cells(i, 2)
Cell1.Offset(0, 1) = Application.Sum(rngSum)
Set Cell1 = Nothing
Set Cell2 = Nothing
End If
i = i + 1
Loop
Else
i = i + 1
End If
Next ar

HTH,
--
George Nicholson

"Tre_cool" <Tre_cool.1x7keh_1129824330.6204@excelforum-nospam.com> wrote in
message news:Tre_cool.1x7keh_1129824330.6204@excelforum-nospam.com...
>
> I'm trying to sum numbers between non zero's but when there are
> multiples in rows it doesn't work. For example row 5 should sum to 6
> and row 6 should sum to 12(1+8+3). However with the code I have below
>
> Thanks
> Trevor
> Eg.
> 1 0 3
> 2 Yes 2
> 3 0 5
> 4 0 2
> 5 Yes 6
> 6 Yes 4
> 7 0 1
> 8 0 8
> 9 0 3
> 10 Yes 2
>
>
> Sub SumBetween()
> ' This macro sums the differences between Yes's
> ' Macro1 Macro
> ' Macro recorded 22-08-2005
>
> 'Clears column V, sum of the differences
> Sheets("WorkPage").Select
> Columns("V:V").Select
> Selection.ClearContents
>
> 'Sets ranges to column P then formats to formula and text cell
> Set rng = Columns(16).SpecialCells(xlFormulas, xlTextValues)
> i = 0
> For Each ar In rng.Areas
> i = i + 1
> If i <> 1 Then
> Set rng1 = Range(cell.Offset(1, 4), ar.Offset(-1, 4))
> cell.Offset(0, 6).Value = Abs(Application.Sum(rng1))
> End If
> Set cell = ar
> Next
> MinPower
> Calculate
> End Sub
>
>
> --
> Tre_cool
> ------------------------------------------------------------------------
> Tre_cool's Profile:
> http://www.excelforum.com/member.php...o&userid=26416
>  Register To Reply

3. George, I agree with you 6 and 16. Let me try out your code.

Thanks!  Register To Reply