Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Use vb to Sum value in column (2003)

    I am seeking a solution to summing the values in one column based on changes in values in another column. I have the first column with a location, when the location changes, I am inserting a blank row (Thanks Hans V!). I have placed a "Total " label in the first column at the new row. Since the number of rows per location varies, I am struggling trying to find a way to insert the "=Sum()" formula and identifying how the actual rows to be included in the sum.

    The following is how I have identified the change in value in first column and inserted the blank row:
    m = .Range("A65536").End(xlUp).Row
    ' Loop backwards
    For r = m To 3 Step -1
    ' Compare cell to cell above it
    If Not .Range("A" & (r - 1)) = .Range("A" & r) Then
    ' If not equal, insert blank row
    .Range("A" & r).EntireRow.Insert
    .Range("A" & ) = "Total " & .Range("A" & (r - 1))
    .Range("A" & ).Font.Bold = True
    'Need to total column L Here ?????
    End If
    Next r
    End If

    I am trying to sum column L, but don't know if this is where I would do the sum function, or if I need to complete the above for/next loop and then loop through again to find the blank cells and insert the sum function at that time.

    I apologize in advance for my ignorance of Excel and VBA.

    Thanks!
    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use vb to Sum value in column (2003)

    Ha! If you had indicated from the start that you wanted to create subtotals, I'd have suggested different code. Excel has a Subtotals feature built in, and this can be manipulated using VBA. For example:
    <code>
    .Range("A2").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(12)
    </code>
    This will insert subtotals each time the value of the first column changes (GroupBy:=1) and sum (Function:=xlSum) the values in the 12th column (TotalList:=Array(12)). No tedious counting of rows!

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Use vb to Sum value in column (2003)

    Hans,

    That is very kewl. What if I want to subtotal more than one column. For example, the 12, 13,14, etc.

    Regards,
    Ken

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use vb to Sum value in column (2003)

    You'd change

    ..., TotalList:=Array(12)

    to

    ..., TotalList:=Array(12, 13, 14)

    In other words, an array of the column offsets for which you want a subtotal.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Use vb to Sum value in column (2003)

    That would have been next guess. I had tried Array(12,5) as in begin at 12 and go for 5. Needless to say it didn't work. LOL

    Thanks again for you help.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use vb to Sum value in column (2003)

    If you click in the word SubTotal in the Visual Basic Editor and press F1, youi'll get help, with an example.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Use vb to Sum value in column (2003)

    Hans,

    Thanks. I tried the F1, and it asks if I want to load help. Unfortunately, I don't have the CD with me, so I can't read up on it.

    I'll load it when I get back home.

    thanks again.

  8. #8
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Use vb to Sum value in column (2003)

    Hans,

    Is there a way to sum columns AND count rows at the same time?

    Thanks,

    Ken

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use vb to Sum value in column (2003)

    Yes, for example like this:
    <code>
    .Range("A2").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(12, 13, 14), Replace:=True
    .Range("A2").Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(12, 13, 14), Replace:=False
    </code>
    The second line has Replace:=False to indicate that the new subtotals should not replace the already existing ones.

  10. #10
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Use vb to Sum value in column (2003)

    that is very kewl. Is there a way to sum a group of columns and at the same time count a different column and have only one line of "totals"?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use vb to Sum value in column (2003)

    Not with the built-in SubTotal method; this always inserts a new row for each subtotal. You'd either have to move the subtotals to one row after using the SubTotal method, or build it all yourself. In both cases, it's extra work, so you should ask yourself if it's worth the effort.

  12. #12
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Use vb to Sum value in column (2003)

    Hans,

    THANKS so much. I have learn so much from you in the past in Access. And now Excel. Until now, I didn't know that you could use the data menu option in Excel to add and take away subtotals for the entire spreadsheet. If I had known this before, it would have saved countless hours. WOW

    and you are correct, I don't believe it is worth the effort at this point to force the count and totals to the same line.

    Thanks again.

    Ken

Posting Permissions

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