Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Subtotal in VBA
I have a spreadsheet used to store data collected and plan to write some VBA
codes associate to a button. I have a test routine using the Filter function as shown in sample below Selection.AutoFilter Field:=1, Criteria1:="MLI" Selection.AutoFilter Field:=6, Criteria1:="Cost Performance Index" Selection.AutoFilter Field:=7, Criteria1:="Computed Metric" Can someone please tell me how can use the "Subtotal" function as if it were on the spreadsheet to sum and average of the above filtered data stored in column J ? Thanks in advance for any assistance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Subtotal in VBA
Hi Wellie;
Try something like: Sub Tester() Dim rng As Range Dim MyTotal As Double Dim MyAverage As Double Const strCol As String = "B" Set rng = ActiveSheet.AutoFilter.Range With rng .AutoFilter Field:=1, Criteria1:="MLI" .AutoFilter Field:=6, Criteria1:="Cost Performance Index" .AutoFilter Field:=7, Criteria1:="Computed Metric" End With With Application MyTotal = .Subtotal(9, rng.Columns(strCol)) MyAverage = Format(.Subtotal(1, rng.Columns(strCol)), "0.00") End With MsgBox "Filter Total= " & MyTotal & vbNewLine _ & "Filter Average = " & MyAverage End Sub Note that this includes no error checking. --- Regards, Norman "Wellie" wrote in message ... I have a spreadsheet used to store data collected and plan to write some VBA codes associate to a button. I have a test routine using the Filter function as shown in sample below Selection.AutoFilter Field:=1, Criteria1:="MLI" Selection.AutoFilter Field:=6, Criteria1:="Cost Performance Index" Selection.AutoFilter Field:=7, Criteria1:="Computed Metric" Can someone please tell me how can use the "Subtotal" function as if it were on the spreadsheet to sum and average of the above filtered data stored in column J ? Thanks in advance for any assistance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Subtotal in VBA
Hi Wellie,
Const strCol As String = "B" Should read: Const strCol As String = "J" --- Regards, Norman "Norman Jones" wrote in message ... Hi Wellie; Try something like: Sub Tester() Dim rng As Range Dim MyTotal As Double Dim MyAverage As Double Const strCol As String = "B" Set rng = ActiveSheet.AutoFilter.Range With rng .AutoFilter Field:=1, Criteria1:="MLI" .AutoFilter Field:=6, Criteria1:="Cost Performance Index" .AutoFilter Field:=7, Criteria1:="Computed Metric" End With With Application MyTotal = .Subtotal(9, rng.Columns(strCol)) MyAverage = Format(.Subtotal(1, rng.Columns(strCol)), "0.00") End With MsgBox "Filter Total= " & MyTotal & vbNewLine _ & "Filter Average = " & MyAverage End Sub Note that this includes no error checking. --- Regards, Norman "Wellie" wrote in message ... I have a spreadsheet used to store data collected and plan to write some VBA codes associate to a button. I have a test routine using the Filter function as shown in sample below Selection.AutoFilter Field:=1, Criteria1:="MLI" Selection.AutoFilter Field:=6, Criteria1:="Cost Performance Index" Selection.AutoFilter Field:=7, Criteria1:="Computed Metric" Can someone please tell me how can use the "Subtotal" function as if it were on the spreadsheet to sum and average of the above filtered data stored in column J ? Thanks in advance for any assistance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal To Include Item Description On Subtotal Line | Excel Discussion (Misc queries) | |||
sort macro, subtotal and add lines after subtotal | Excel Discussion (Misc queries) | |||
pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions | |||
Sort, Subtotal, Label Subtotal, Insert row | Excel Programming |