Subtotal function macro

Marlowwe

New Member
Joined
Jan 12, 2016
Messages
46
Hello,

I have macro with Subtotal function

1)run macro
2) select cell for subtotal
3) Select type of subtotal
4 ) Select area for subtotal
5) in selected cell from phase 2) should be imported subtotal, but shows error in row ActiveCell.Formula .... , pls help me correct the macro:

Code:
Sub SUBTOTAL_V2()
'NEfunguje
Dim myValue As Range
Dim myType As Variant
Dim rng As Range


Set myValue = Application.InputBox("Cell to put SUBTOTAL", Type:=8)
myType = InputBox("Type of subtotal:" & vbCrLf & _
"1 - AVERAGE" & vbCrLf & _
"2 - COUNT" & vbCrLf & _
"3 - COUNTA" & vbCrLf & _
"4 - MAX" & vbCrLf & _
"5 - MIN" & vbCrLf & _
"9 - SUM" & vbCrLf & _
"For ignoring hidden values put before 10, for example 101, 102 etc.", "GleedsTool", "9")
Set rng = Application.InputBox("Area", Type:=8)


myValue.Select


ActiveCell.Formula = "=subtotal(" & myValuee & "," & rng & ")"
End Sub

Thank you
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hello,


The edited code below should do what you need it to.


I've changed mytype to be a string instead of a variant.


Then instead of using activecell I have specified the cell to change directly with myvalue.formula, the selection isn't needed it's not wrong, but personally I think stating the range being edited rather than activecell is more readable.


Now when applying the formula to the cell the subtotal formula should read something link =subtotal(9,A4:D9)
9 being your subtotal type so mytype and A4:D9 being your range of values so rng.address (Note: .address is needed as rng is a range not a string so you need .address to retrieve the range selected)




Code Snippet (Edited in red)


Sub SUBTOTAL_V2()
'NEfunguje
Dim myValue As Range
Dim myType As String
Dim rng As Range


Set myValue = Application.InputBox("Cell to put SUBTOTAL", Type:=8)
myType = InputBox("Type of subtotal:" & vbCrLf & _
"1 - AVERAGE" & vbCrLf & _
"2 - COUNT" & vbCrLf & _
"3 - COUNTA" & vbCrLf & _
"4 - MAX" & vbCrLf & _
"5 - MIN" & vbCrLf & _
"9 - SUM" & vbCrLf & _
"For ignoring hidden values put before 10, for example 101, 102 etc.", "GleedsTool", "9")
Set rng = Application.InputBox("Area", Type:=8)


myValue.Formula = "=subtotal(" & myType & "," & rng.Address & ")"
myValue.Select


End Sub
 
Upvote 0
A more advanced version with 'Cancel' handling and myType validation:
Code:
Sub SUBTOTAL_V2a()
'NEfunguje
Dim myValue As Range
Dim myType As Variant
Dim rng As Range
'
On Error GoTo EndLine
Set myValue = Application.InputBox("Cell to put SUBTOTAL", Type:=8)
On Error GoTo 0
'
myType = Application.InputBox("Type of subtotal:" & vbCrLf & _
    "1 - AVERAGE" & vbCrLf & _
    "2 - COUNT" & vbCrLf & _
    "3 - COUNTA" & vbCrLf & _
    "4 - MAX" & vbCrLf & _
    "5 - MIN" & vbCrLf & _
    "9 - SUM" & vbCrLf & _
    "For ignoring hidden values put before 10, for example 101, 102 etc.", _
    "GleedsTool", "9", Type:=1)
If myType = False Then Exit Sub
'
On Error GoTo EndLine
Set rng = Application.InputBox("Area", Type:=8)
On Error GoTo 0
'
myValue.Select
ActiveCell.Formula = "=SUBTOTAL(" & myType & "," & rng.Address & ")"
'
EndLine:
End Sub
 
Upvote 0
Hello,


The edited code below should do what you need it to.


I've changed mytype to be a string instead of a variant.
....


A more advanced version with 'Cancel' handling and myType validation:
Code:
Sub SUBTOTAL_V2a()
'NEfunguje
Dim myValue As Range
Dim myType As Variant
D
...

Thank you very much It works perfectly. Can you give me another help, how to modify to : The same macro, but the selection o cells is automatic inserted in subtotal function in macro. Thank you :)
 
Upvote 0
Hi, I'm not 100% clear on what you need, do you mean that the range being filtered will not be changed so you want it to be fixed rather than asking the user.

If this is the case then replace

myValue.Formula = "=subtotal(" & myType & "," & rng.Address & ")"

with

myValue.Formula = "=subtotal(" & myType & ",$A$1:$E$50)"

$A$1:$E$50 being your data range being looked at
 
Upvote 0
oh and remove/comment out this line to stop the question being prompted

Set rng = Application.InputBox("Area", Type:=8)

 
Upvote 0
Thank you for your answer.

I mean:

1) I select some area for example A1,A2,A3 an want in cell A4 subtotal. (But areas should be different).
2) Run mine macro and select Where tu put the subtotal: A4
3) insert type of subtotal: 9
And the macro write subtotal (in cell A4) with data range selected before the run macro (point 1)


Thank oyu
 
Upvote 0
I think I follow, try this, I've set rng (data range) to be whatever you have selected before you run the macro. Then the macro just asks for where to put the subtotal and what type it should be.

Code Snippet

Sub SUBTOTAL_V2()
'NEfunguje
Dim myValue As Range
Dim myType As String
Dim rng As Range


Set rng = Application.Selection


Set myValue = Application.InputBox("Cell to put SUBTOTAL", Type:=8)
myType = InputBox("Type of subtotal:" & vbCrLf & _
"1 - AVERAGE" & vbCrLf & _
"2 - COUNT" & vbCrLf & _
"3 - COUNTA" & vbCrLf & _
"4 - MAX" & vbCrLf & _
"5 - MIN" & vbCrLf & _
"9 - SUM" & vbCrLf & _
"For ignoring hidden values put before 10, for example 101, 102 etc.", "GleedsTool", "9")


myValue.Formula = "=subtotal(" & myType & "," & rng.Address & ")"
myValue.Select


End Sub
 
Upvote 0
I think I follow, try this, I've set rng (data range) to be whatever you have selected before you run the macro. Then the macro just asks for where to put the subtotal and what type it should be.

lect


End Sub


Thank you very much, works perfectly :)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top