Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Summing columns until all totalled

    Hi
    I have been thrown a little challenge which is to sum variable cells at variable locations until all complete.

    Background
    A spreadsheet is created from our CRM database. It has 19 columns and an endless number of rows representing jobs carried out in specific areas for organisations and each group of jobs separated by a lank row.

    The challenge
    To add a new row after each block, sum columns 11, 12 & 13, copy these totals to columns 21, 22 & 23. Repeat for each of the 17 groups, total columns 21, 22 $ 23

    The logic I have worked out:
    1. Work in column 1
    2. Search down for next blank cell
    3. Check if next cell down contains data, if yes then continue, else goto 5
    4. Insert row above and move up 1 row
    5. Move to column 11
    6. Sum above for columns 11, 12 & 13
    7. Copy sub totals in columns 11, 12 & 13 to columns 21, 22 & 23
    8. Repeat until finished
    9. Move down 1 row
    10. Move to column 21
    11. Sum above for columns 21, 22 & 23
    12. End


    I hope this makes sense! While I understand the logic the code is a little above me
    cheers

    Phil Carter

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Bonriki,

    Hope this is what you are looking for, The code will find the blank row between blocks of jobs and add a new row. It will then total the values in columns 11, 12, and 13 for each job and place those values in the inserted row at columns 21, 22, and 23. Finally, it will vertically sum all the subtotals for each column at the bottom. I have place buttons on the sheet to start the code and reset the sheet. You can add any number of additional rows to the job blocks or add as many new blocks as you wish.

    HTH,
    Maud

    totalsheet1.png

    totalsheet2.png
    Code:
    Public Sub TotalBlocks()
    'SET VARIABLES
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    '-----------------------------------------------------------------
    'FIND STARTING ROW
    Row = 1
    If [a1] = "" Then Row = 2
    '-----------------------------------------------------------------
    'FIND NEXT AVAILABLE ROW AND SUM BLOCKS
    For I = 1 To LastRow
        AvailableRow = Range("A" & Row).End(xlDown).Row + 1
        Rows(AvailableRow & ":" & AvailableRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Cells(AvailableRow, 20) = "Subtotal:"
        Cells(AvailableRow, 21) = WorksheetFunction.Sum(Range(Cells(Row, 11), Cells(AvailableRow - 1, 11)))
        Cells(AvailableRow, 22) = WorksheetFunction.Sum(Range(Cells(Row, 12), Cells(AvailableRow - 1, 12)))
        Cells(AvailableRow, 23) = WorksheetFunction.Sum(Range(Cells(Row, 13), Cells(AvailableRow - 1, 13)))
        Row = AvailableRow + 2
        LastRow = LastRow + 1
        If AvailableRow = LastRow Then Exit For
    Next I
    '-----------------------------------------------------------------
    'SUM COLUMNS
    Cells(LastRow + 1, 20) = "Total:"
    Cells(LastRow + 1, 21) = WorksheetFunction.Sum(ActiveSheet.Columns(21))
    Cells(LastRow + 1, 22) = WorksheetFunction.Sum(ActiveSheet.Columns(22))
    Cells(LastRow + 1, 23) = WorksheetFunction.Sum(ActiveSheet.Columns(23))
    End Sub
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Maud
    Thanks for that exactly what I was looking for
    cheers

    Phil Carter

  4. #4
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Maud
    I have been doing some testing and have found a few anomalies.
    1. Routine not functioning as required or as your demo file runs. See images below
    TotalBlocks.JPG

    TotalBlocks2.JPG
    The shots are taken at the top and near the bottom of spreadsheet
    I didn't mention that the first row was column headings, not sure if that should make difference

    2. Routine fails before totalling columns U, V & W
    TotalBlocksFail.JPG

    The output is direct from our CRM which names the program in column B and then repeats it for every organisation in column A.
    It is interesting that code only inserts a blank line and totals the one organisation for some programs
    cheers

    Phil Carter

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Is it possible to see how the sheet was set up before the code was run? What was the error message you received? Will the first block always start on line 3?

    Maud

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    The problem was detecting the next available blank line when only one job was in the block.

    I think we've got it now!

    Good Luck,
    Maud
    Attached Files Attached Files

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    bonriki (2013-11-10)

  8. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Maud hi
    That's great.
    As I stated earlier the first line is a heading line and routine now inserts a line under the heading and totals it. Just a bit annoying but doesn't affect the functionality.
    FYI the transferred file has up to 3000 records with approx 75 programs and is used for forward planning, so very useful!

    Thanks again
    cheers

    Phil Carter

  9. #8
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Oops!!
    Just discovered your routine for the sub-totals actually accumulates the totals. Not really ideal
    Accumulate.JPG
    cheers

    Phil Carter

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    bonriki,

    Wow, never realized that. I fixed the header problem and changed line number 7 from AvailableRow = 1 to AvailableRow = 3 where 3 should be the first line of the first block. I also added an additional variable, StartRow, to remember where each block started so that the subtotal will start from that point and not the top of the sheet. Hopefully, all the bugs have been worked out.

    Maud
    Attached Files Attached Files

  11. The Following User Says Thank You to Maudibe For This Useful Post:

    bonriki (2013-11-11)

Posting Permissions

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