+ Reply to Thread
Results 1 to 3 of 3

HELP! Almost got it..Summing numbers between

  1. #1
    Registered User
    Join Date
    08-18-2005
    Posts
    22

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

  2. #2
    George Nicholson
    Guest

    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
    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" <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
    > 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. #3
    Registered User
    Join Date
    08-18-2005
    Posts
    22
    George, I agree with you 6 and 16. Let me try out your code.

    Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1