HELP! Almost got it..Summing numbers between
 

I'm trying to sum numbers between non zero's but when there ar
multiples in rows it doesn't work. For example row 5 should sum to
and row 6 should sum to 12(1+8+3). However with the code I have belo
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
MinAdd (myAdd)
MinPower
Calculate
End Su

--
Tre_coo
-----------------------------------------------------------------------
Tre_cool's Profile: http://www.excelforum.com/member.php...fo&userid=2641
View this thread: http://www.excelforum.com/showthread.php?threadid=47792

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
for your layout as necessary.

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)
Set rngSum = ActiveSheet.Range(Cell1.Address,
Cell2.Address)
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

Remove 'Junk' from return address.


"Tre_cool" wrote in
message ...

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
MinAdd (myAdd)
MinPower
Calculate
End Sub


--
Tre_cool
------------------------------------------------------------------------
Tre_cool's Profile:
http://www.excelforum.com/member.php...o&userid=26416
View this thread: http://www.excelforum.com/showthread...hreadid=477926

HELP! Almost got it..Summing numbers between
 

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

Thanks!


--
Tre_cool
------------------------------------------------------------------------
Tre_cool's Profile: http://www.excelforum.com/member.php...o&userid=26416
View this thread: http://www.excelforum.com/showthread...hreadid=477926