Summing columns until all totalled

Topic

New Reply

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:

[*]Work in column 1
[*]Search down for next blank cell
[*]Check if next cell down contains data, if yes then continue, else goto 5
[*]Insert row above and move up 1 row
[*]Move to column 11
[*]Sum above for columns 11, 12 & 13
[*]Copy sub totals in columns 11, 12 & 13 to columns 21, 22 & 23
[*]Repeat until finished
[*]Move down 1 row
[*]Move to column 21
[*]Sum above for columns 21, 22 & 23
[*]End

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

Reply | Quote

Replies

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

35375-totalsheet1

35376-totalsheet2

Code:

Public Sub TotalBlocks()
[COLOR=”#008000″]’SET VARIABLES[/COLOR]
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
[COLOR=”#008000″]’—————————————————————–
‘FIND STARTING ROW[/COLOR]
Row = 1
If [a1] = “” Then Row = 2
[COLOR=”#008000″]’—————————————————————–
‘FIND NEXT AVAILABLE ROW AND SUM BLOCKS[/COLOR]
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
[COLOR=”#008000″]’—————————————————————–
‘SUM COLUMNS[/COLOR]
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

Reply | Quote

Maud
Thanks for that exactly what I was looking for

Reply | Quote

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
35384-TotalBlocks

35385-TotalBlocks2
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
35383-TotalBlocksFail

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Oops!!
Just discovered your routine for the sub-totals actually accumulates the totals. Not really ideal
35432-Accumulate

Reply | Quote

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

Reply | Quote