Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cal formula upon sorting
Hi,
The question I have is a follow-up to my previous question at URL, http://groups.google.com/group/micro...90e60d9327284e In case of url problem, the idea and objective is explained he 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cal formula upon sorting
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 " wrote: Hi, The question I have is a follow-up to my previous question at URL, http://groups.google.com/group/micro...90e60d9327284e In case of url problem, the idea and objective is explained he 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cal formula upon sorting
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? On Sep 15, 4:40 pm, Joel wrote: 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 OP omitted - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cal formula upon sorting
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. " wrote: 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? On Sep 15, 4:40 pm, Joel wrote: 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 OP omitted - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cal formula upon sorting
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 On Sep 16, 12:50 am, Joel wrote: 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. " wrote: 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cal formula upon sorting
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. " wrote: 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 On Sep 16, 12:50 am, Joel wrote: 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. " wrote: 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cal formula upon sorting
Works beautiful, Joel, thank you and sorry I was a bit lazy.
Don On Sep 17, 5:44 am, Joel wrote: 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. " wrote: omitted - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting and formula reference | Excel Discussion (Misc queries) | |||
Sorting the cells of a formula causes the formula to not work | Excel Worksheet Functions | |||
I need help with a formula or a sorting action | Excel Discussion (Misc queries) | |||
sorting with a formula | Excel Discussion (Misc queries) | |||
Sorting formula? | Excel Discussion (Misc queries) |