Cal formula upon sorting

[DonLi2006]

Hi,

The question I have is a follow-up to my previous question at URL,
http://groups.google.com/group/micr...60d9327284e?lnk=st&q=&rnum=1#4490e60d9327284e

In case of url problem, the idea and objective is explained here:
Case/Data/Background
C5 = column 5, is a formula that calculate the total minutes (for the
duration between C3 and C4), using Tom from Microsoft's formual of
MOD(c4-c3,1)*24*60.

C1(Date)C2(Task) C3(start) C4(end) C5(cal)
09/11 CF8 17:20 19:00 100
09/12 ink 21:30 23:00 90
9/14 CF8 15:20 18:30 190
New Objective:
Now, if I want to sort by C2 (column 2, Task)
We'll get the following,
C1(Date)C2(Task) C3(start) C4(end) C5(cal)
09/11 CF8 17:20 19:00 100
9/14 CF8 15:20 18:30 190
09/12 ink 21:30 23:00 90
That's helpful but I'd like Excel to automatically insert a row right
below each Task and does automatical subtotalling for the task, the
desired state would look this:
C1(Date)C2(Task) C3(start) C4(end) C5(cal)
9/11 CF8 17:20 19:00 100
9/14 CF8 15:20 18:30 190
290
9/12 ink 21:30 23:00 90
90

How can I do that?

Many thanks.

 

[Guest]

I think adding the sub-total uner each section makes it hard to read.
Normally people put the sub total in a new column to the right of the data.
I wrote the code both ways. You choose which you like best

Sub addsubtotal1()
'Put data in column F

RowCount = 2
StartRow = 2
Do While Cells(RowCount, "A") <> ""

If (Cells(RowCount, "B") <> _
Cells(RowCount + 1, "B")) Then

Cells(RowCount, "F").Formula = _
"=Sum(E" & StartRow & ":E" & _
RowCount & ")"
StartRow = RowCount + 1

End If

RowCount = RowCount + 1
Loop
End Sub


Sub addsubtotal2()
'Put data in new row

RowCount = 2
StartRow = 2
Do While Cells(RowCount, "A") <> ""

If (Cells(RowCount, "B") <> _
Cells(RowCount + 1, "B")) Then

Rows(RowCount + 1).Insert
Cells(RowCount + 1, "E").Formula = _
"=Sum(E" & StartRow & ":E" & _
RowCount & ")"

RowCount = RowCount + 2
StartRow = RowCount
Else
RowCount = RowCount + 1
End If

Loop
End Sub

 

[DonLi2006]

Thank you, Joel.

I went to the Tools/Macro/Visual Basic Editor and add your following
option A as a module then run the macro, it did not do anything.
Column F is also my calc formula column, and yes, call it Column F is
better, sorry I'm from db world... And my data starts from Row 4...

What did I miss?

 

[Guest]

There are 3 reason it may not have run
1) It works on the active worksheet. Yoy may have been on another sheet.
2) The code need to be on a module sheet in VBA. Look at the Project window
and add a module page in the workbook that you want to run the code. Module
can be added from the Insert menu or right clicking the workbook in the
project window.
3) Your security mode may be set to high
4) Go to spreadsheet window. Go to tools macro security and change the
security level to medium. SAve the wroklbook. Then close and open the
workbook. When the workbook opens press enable macros.

 

[DonLi2006]

Hi Joel,

I followed all these steps, still to no avail. btw, my Excel version
is 2000. What else could stand in the way?

Many thanks.

Don

 

[Guest]

stepping through the code and debugging is the next step.

1) Open the VBA window and click any line inside the macro.
2) pressing F8 will step through the code.
3) to see the values of the variables, right click the variable and then
select add watch. Add rowcount and startrow as two of the watch variables.

I suspect cell A2 is empty. Let me know how far the code runs.

 

[DonLi2006]

The code runs fine without any error (step through w/ F8 key). And
yes, initially my worksheet has column A blank. So, I copy the data
to a new sheet which reads like
A B C D E F G
1 9/11 CF8 17:20 19:00 100
2 9/13 CF8 10:00 12:00 120
3 9/14 CF8 15:20 18:30 190
4 9/15 CMD 13:00 14:50 110
5 fk bind 21:20 23:00 100
then ran the macro/module against this new sheet only, odd, value in
G3 is now "0:00" and G4 is also "0:00"
I changed the sum to use F column and G for subtotal display.
Probably I wasn't clear enough, F column uses the following formula,
=MOD(D1-C1,1)*24*60

What else can we try, Joel, many thanks.

Don

 

[Guest]

Your problem is solved. Just change the format of colum F & G from time to
number. When you add or Sutract time the results is a fraction of a day. 8
hours is 8/24 = .333 days. Like you did in your formula you have to multiply
by 24 * 60 to get minutes. The minutes are no longer a time format after the
conversion, but a number format in minutes.

I think you may need to change this line below to multiply by 24 * 60 like
you did in your fomula.
Cells(RowCount, "F").Formula = _
"=Sum(E" & StartRow & ":E" & _
RowCount & ")"

I think total time sub total is really a subtraction as follows:

Cells(RowCount, "F").Formula = _
"=24*60*(F" & RowCount "-F" & StartRow & ")"

You really should change the format to column D to a date - time format such
as
3/14/01 1:30PM. This will make it easier to calculate the sub total time.

 

[DonLi2006]

Works beautiful, Joel, thank you and sorry I was a bit lazy.

Don