Subtotals and inserting formulae

[Guest]

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]

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?

 

[Guest]

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]

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!)

 

[Guest]

Thanks, will give this a try!

-jandro

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!)