Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing numbers | Excel Discussion (Misc queries) | |||
Keep getting #### when summing numbers | Excel Discussion (Misc queries) | |||
summing more than 30 numbers | Excel Discussion (Misc queries) | |||
summing numbers | Excel Programming | |||
Summing Numbers | Excel Discussion (Misc queries) |