Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotals and inserting formulae
Greetings!
I am mooking for some code/advice on inserting code into the subtotal rows created by SUBTOTAL. Is there a way to autofill the formulae in the rows created by the function (the subtotal and Grand Total rows)? I work for a human services agency that tracks time spent with clients in 15 minute increments rounded to the next integer. Subtotal is a great way to quickly sort and add these minutes together, but it cannot be expected that the end users will be able to autofill formulae correctly. I am looking for a way to insert some code that will sum the time units rather than the minutes on the Grand Total row . Being as there are different numbers of clients seen by each case manager on a day-to-day basis (resulting in a variable number of rows filled out on the worksheet), it would be ideal to find a way to insert a function in the last row created by Subtotal. I hope this made sense. Thanking you in advance, -alex |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotals and inserting formulae
I don't know what a time unit is, but can you add another column that converts
minutes to units and just include that in your range to data|subtotal? jandro wrote: Greetings! I am mooking for some code/advice on inserting code into the subtotal rows created by SUBTOTAL. Is there a way to autofill the formulae in the rows created by the function (the subtotal and Grand Total rows)? I work for a human services agency that tracks time spent with clients in 15 minute increments rounded to the next integer. Subtotal is a great way to quickly sort and add these minutes together, but it cannot be expected that the end users will be able to autofill formulae correctly. I am looking for a way to insert some code that will sum the time units rather than the minutes on the Grand Total row . Being as there are different numbers of clients seen by each case manager on a day-to-day basis (resulting in a variable number of rows filled out on the worksheet), it would be ideal to find a way to insert a function in the last row created by Subtotal. I hope this made sense. Thanking you in advance, -alex -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotals and inserting formulae
That would work, but it would be ideal to find a way to plug functions into
the rows created by Subtotal to create a sheet that's easier to read. BTW - time units are not a direct function of aggregate minutes. Minutes are summed per client per diem and converted into 15 min units. Hence, it is possible to bill more time (units) by spending shorter amounts of time with more clients over the course of a day than by spending eight hours with one client. Thanks for your help so far. -jandro "Dave Peterson" wrote: I don't know what a time unit is, but can you add another column that converts minutes to units and just include that in your range to data|subtotal? jandro wrote: Greetings! I am mooking for some code/advice on inserting code into the subtotal rows created by SUBTOTAL. Is there a way to autofill the formulae in the rows created by the function (the subtotal and Grand Total rows)? I work for a human services agency that tracks time spent with clients in 15 minute increments rounded to the next integer. Subtotal is a great way to quickly sort and add these minutes together, but it cannot be expected that the end users will be able to autofill formulae correctly. I am looking for a way to insert some code that will sum the time units rather than the minutes on the Grand Total row . Being as there are different numbers of clients seen by each case manager on a day-to-day basis (resulting in a variable number of rows filled out on the worksheet), it would be ideal to find a way to insert a function in the last row created by Subtotal. I hope this made sense. Thanking you in advance, -alex -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotals and inserting formulae
I think I would find that confusing--the header for that field would say
minutes/time, but the subtotals would be units. I'd still use that extra column to remove any possible misinterpretation. Do you enter the minutes as numbers (32 for 32 minutes) or do you enter times (0:32 for 32 minutes). If it's 32 for 32 minutes, this worked ok for me: Option Explicit Sub testme() Dim myRng As Range Dim myCell As Range Dim GrandTotal As Long Dim cCtr As Long Dim maxCells As Long With Worksheets("sheet1") Set myRng = Nothing On Error Resume Next Set myRng = .Columns(2).Cells _ .SpecialCells(xlCellTypeFormulas, xlNumbers) On Error GoTo 0 If myRng Is Nothing Then MsgBox "no formulas that evaluate to numbers!" Exit Sub End If cCtr = 0 maxCells = myRng.Cells.Count For Each myCell In myRng.Cells With myCell If LCase(.Formula) Like "=subtotal(*" Then cCtr = cCtr + 1 If cCtr < maxCells Then 'found one to adjust. '=SUMPRODUCT(ROUNDUP(B2:B6/15,0)*15) .Formula = "=sumproduct(roundup(" _ & .Precedents.Address & "/15,0)*15)" GrandTotal = GrandTotal + .Value Else .Value = GrandTotal End If End If End With Next myCell End With End Sub If you enter 0:32, then change this portion: .Formula = "=sumproduct(roundup(" _ & .Precedents.Address & "/15,0)*15)" to .Formula = "=sumproduct(roundup(" _ & .Precedents.Address & _ "/TIME(0,15,0),0)*TIME(0,15,0))" It's using a formula like: '=sumproduct(ROUNDUP(B2:B6/TIME(0,15,0),0)*TIME(0,15,0)) But when you make a change to your data, you'll have to reapply data|subtotal (replace existing) and rerun the code. (I'd use that other column!) jandro wrote: That would work, but it would be ideal to find a way to plug functions into the rows created by Subtotal to create a sheet that's easier to read. BTW - time units are not a direct function of aggregate minutes. Minutes are summed per client per diem and converted into 15 min units. Hence, it is possible to bill more time (units) by spending shorter amounts of time with more clients over the course of a day than by spending eight hours with one client. Thanks for your help so far. -jandro "Dave Peterson" wrote: I don't know what a time unit is, but can you add another column that converts minutes to units and just include that in your range to data|subtotal? jandro wrote: Greetings! I am mooking for some code/advice on inserting code into the subtotal rows created by SUBTOTAL. Is there a way to autofill the formulae in the rows created by the function (the subtotal and Grand Total rows)? I work for a human services agency that tracks time spent with clients in 15 minute increments rounded to the next integer. Subtotal is a great way to quickly sort and add these minutes together, but it cannot be expected that the end users will be able to autofill formulae correctly. I am looking for a way to insert some code that will sum the time units rather than the minutes on the Grand Total row . Being as there are different numbers of clients seen by each case manager on a day-to-day basis (resulting in a variable number of rows filled out on the worksheet), it would be ideal to find a way to insert a function in the last row created by Subtotal. I hope this made sense. Thanking you in advance, -alex -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotals and inserting formulae
Thanks, will give this a try!
-jandro "Dave Peterson" wrote: I think I would find that confusing--the header for that field would say minutes/time, but the subtotals would be units. I'd still use that extra column to remove any possible misinterpretation. Do you enter the minutes as numbers (32 for 32 minutes) or do you enter times (0:32 for 32 minutes). If it's 32 for 32 minutes, this worked ok for me: Option Explicit Sub testme() Dim myRng As Range Dim myCell As Range Dim GrandTotal As Long Dim cCtr As Long Dim maxCells As Long With Worksheets("sheet1") Set myRng = Nothing On Error Resume Next Set myRng = .Columns(2).Cells _ .SpecialCells(xlCellTypeFormulas, xlNumbers) On Error GoTo 0 If myRng Is Nothing Then MsgBox "no formulas that evaluate to numbers!" Exit Sub End If cCtr = 0 maxCells = myRng.Cells.Count For Each myCell In myRng.Cells With myCell If LCase(.Formula) Like "=subtotal(*" Then cCtr = cCtr + 1 If cCtr < maxCells Then 'found one to adjust. '=SUMPRODUCT(ROUNDUP(B2:B6/15,0)*15) .Formula = "=sumproduct(roundup(" _ & .Precedents.Address & "/15,0)*15)" GrandTotal = GrandTotal + .Value Else .Value = GrandTotal End If End If End With Next myCell End With End Sub If you enter 0:32, then change this portion: .Formula = "=sumproduct(roundup(" _ & .Precedents.Address & "/15,0)*15)" to .Formula = "=sumproduct(roundup(" _ & .Precedents.Address & _ "/TIME(0,15,0),0)*TIME(0,15,0))" It's using a formula like: '=sumproduct(ROUNDUP(B2:B6/TIME(0,15,0),0)*TIME(0,15,0)) But when you make a change to your data, you'll have to reapply data|subtotal (replace existing) and rerun the code. (I'd use that other column!) jandro wrote: That would work, but it would be ideal to find a way to plug functions into the rows created by Subtotal to create a sheet that's easier to read. BTW - time units are not a direct function of aggregate minutes. Minutes are summed per client per diem and converted into 15 min units. Hence, it is possible to bill more time (units) by spending shorter amounts of time with more clients over the course of a day than by spending eight hours with one client. Thanks for your help so far. -jandro "Dave Peterson" wrote: I don't know what a time unit is, but can you add another column that converts minutes to units and just include that in your range to data|subtotal? jandro wrote: Greetings! I am mooking for some code/advice on inserting code into the subtotal rows created by SUBTOTAL. Is there a way to autofill the formulae in the rows created by the function (the subtotal and Grand Total rows)? I work for a human services agency that tracks time spent with clients in 15 minute increments rounded to the next integer. Subtotal is a great way to quickly sort and add these minutes together, but it cannot be expected that the end users will be able to autofill formulae correctly. I am looking for a way to insert some code that will sum the time units rather than the minutes on the Grand Total row . Being as there are different numbers of clients seen by each case manager on a day-to-day basis (resulting in a variable number of rows filled out on the worksheet), it would be ideal to find a way to insert a function in the last row created by Subtotal. I hope this made sense. Thanking you in advance, -alex -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto fill formulae when inserting rows | Excel Discussion (Misc queries) | |||
Inserting worksheet makes formulae stop working | Excel Discussion (Misc queries) | |||
Concerning subtotals and inserting | Excel Discussion (Misc queries) | |||
Inserting new row but keeping formulae | Excel Worksheet Functions | |||
inserting formulae | Excel Worksheet Functions |