Macro to open the Subtotal function

[Juan]

I frequently use this function: "=subtotal(9,[range address of cells to be
subtotaled])". This takes 13 keystrokes, not counting the range, which I
usually enter with the mouse.

What I need is a macro that will enter this: "=subtotal(9," and then
let me provide the range (with the mouse or the keyboard) and the closing
")".

I tried this subprocedure: ActiveCell.Formula = "=subtotal(9,". But that
produces an error.

The most workable solution that I've found is this: ActiveCell.Formula =
"=subtotal(9,x)"

Then, when this macro puts this into a cell, I edit the cell (F2), backspace
out the ")" and the "x", and then use the mouse to enter the actual range and
the ")". This is about 6 keystrokes (including the shortcut CTRL + S to run
the macro). That's better than 13 key strokes, but it would be even better
if the macro would start the function and just leave it for me to enter the
range.

Surely, there's a way to do it. But I don't know what it is.
Juan C.

 

[gimme_this_gimme_that]

Did you try selecting the region and clicking the sigma icon (AutoSum)?

The sigma icon looks like this:

-----
\
\
/
/
-----

 

[Juan]

Thanks gimme, but that produces an "=sum()" function. What I hope to find is
how to write a macro that would work the same way but instead of doing the
"=sum()" function over the selected range, it would do the "=subtotal(9, )"
function.

I don't know if you're familiar with the difference between these two or
not, so forgive me if I'm telling you something here that you already know:
The "=sum()" function will produce a sum of all the values of all the cells
in the specified range. But the "=subtotal(9, )" function will ignore any
cell in the range that uses the subtotal function and just produce the sum of
the values of the other cells in the range.

So, for example, let cells A1, A2, and A3 have the values 10, 5, and 5. And
let cell A4 have the function "=subtotal(9,A1:A3), then cell A4 will display
"20". Then leave cell A5 blank and let cell A6, A7, and A8 have the values
25, 30, and 20. And let cell A9 have the function "=subtotal(9,A6:A8), so
that cell A9 will display "75". Now skip cell A10, and let cell A11 have the
function "=subtotal(9,A1:A9)", then cell A11 will display "95".

But, if you were to use the "=sum()" function in place of the
"=subtotal(9,)" function in the above example, then cell A11 will display
"190", which would not be the answer I would want if cell A11 was supposed to
be the total of the two previous subtotals.

If you actually did the above example, then you typed the same "=subtotal(9,
)" three times. That's 13 keystrokes times 3, or 39 keystrokes. So what I
need is a macro to quickly set up the "=subtotal(9,) function like the Sigma
button on the toolbar does for the "=sum()" function.

Any ideas? (And thanks again for giving it a shot.)


Juan C.

 

[John Bundy]

Record a macro, and by ctl put a q so ctl q will start the macro, next
replace the code there with Userform1.show
create a userform with a RefEdit control and a button double click the
button a put
ActiveCell.Formula = "=subtotal(9," & RefEdit1.Value & ")"
Now on the cell you are in hit ctl-q hold shift to highlight your range and
press the button, you can change the buttons default property to true to
allow you to hit enter instead of clicking.

 

[JMB]

You could try:

Sub Test()
Dim rngInput As Range
Dim strAddress As String

On Error Resume Next
Set rngInput = Application.InputBox("Select Range", Type:=8)
On Error GoTo 0
If Not rngInput Is Nothing Then
If rngInput.Parent.Name <> ActiveSheet.Name Then
strAddress = rngInput.Address(False, False, xlA1, True)
Else: strAddress = rngInput.Address(False, False)
End If
ActiveCell.Formula = "=Subtotal(9," & _
strAddress & ")"
End If

End Sub

 

[Juan]

That did perfectly. Wish I understood everything that you did, and why. But
your Sub Test() did exactly the thing I wanted it to do. Thank you.
--
Juan C.

You could try:

Sub Test()
Dim rngInput As Range
Dim strAddress As String

On Error Resume Next
Set rngInput = Application.InputBox("Select Range", Type:=8)
On Error GoTo 0
If Not rngInput Is Nothing Then
If rngInput.Parent.Name <> ActiveSheet.Name Then
strAddress = rngInput.Address(False, False, xlA1, True)
Else: strAddress = rngInput.Address(False, False)
End If
ActiveCell.Formula = "=Subtotal(9," & _
strAddress & ")"
End If

End Sub

I frequently use this function: "=subtotal(9,[range address of cells to be
subtotaled])". This takes 13 keystrokes, not counting the range, which I
usually enter with the mouse.

What I need is a macro that will enter this: "=subtotal(9," and then
let me provide the range (with the mouse or the keyboard) and the closing
")".

I tried this subprocedure: ActiveCell.Formula = "=subtotal(9,". But that
produces an error.

The most workable solution that I've found is this: ActiveCell.Formula =
"=subtotal(9,x)"

Then, when this macro puts this into a cell, I edit the cell (F2), backspace
out the ")" and the "x", and then use the mouse to enter the actual range and
the ")". This is about 6 keystrokes (including the shortcut CTRL + S to run
the macro). That's better than 13 key strokes, but it would be even better
if the macro would start the function and just leave it for me to enter the
range.

Surely, there's a way to do it. But I don't know what it is.
Juan C.

 

[JMB]

You're welcome. Feel free to change "Test" to whatever you want. Thanks for
the feedback.

That did perfectly. Wish I understood everything that you did, and why. But
your Sub Test() did exactly the thing I wanted it to do. Thank you.
--
Juan C.

You could try:

Sub Test()
Dim rngInput As Range
Dim strAddress As String

On Error Resume Next
Set rngInput = Application.InputBox("Select Range", Type:=8)
On Error GoTo 0
If Not rngInput Is Nothing Then
If rngInput.Parent.Name <> ActiveSheet.Name Then
strAddress = rngInput.Address(False, False, xlA1, True)
Else: strAddress = rngInput.Address(False, False)
End If
ActiveCell.Formula = "=Subtotal(9," & _
strAddress & ")"
End If

End Sub

I frequently use this function: "=subtotal(9,[range address of cells to be
subtotaled])". This takes 13 keystrokes, not counting the range, which I
usually enter with the mouse.

What I need is a macro that will enter this: "=subtotal(9," and then
let me provide the range (with the mouse or the keyboard) and the closing
")".

I tried this subprocedure: ActiveCell.Formula = "=subtotal(9,". But that
produces an error.

The most workable solution that I've found is this: ActiveCell.Formula =
"=subtotal(9,x)"

Then, when this macro puts this into a cell, I edit the cell (F2), backspace
out the ")" and the "x", and then use the mouse to enter the actual range and
the ")". This is about 6 keystrokes (including the shortcut CTRL + S to run
the macro). That's better than 13 key strokes, but it would be even better
if the macro would start the function and just leave it for me to enter the
range.

Surely, there's a way to do it. But I don't know what it is.
Juan C.

 

[Juan]

Hey JMB, I've already changed it from Sub Test () to Sub subtotal(), saved it
to the Personal Macro Workbook (where it will be available to all workbooks),
given it a shortcut key, and used it three or four times. It's totally
slick, especially when I consider all the hours I spent in utter frustration
trying to figure out how to do it by myself.

If you've got time, and are willing, I wonder if you could explain what it
is you're doing with this section of the code:

If Not rngInput Is Nothing Then
If rngInput.Parent.Name <> ActiveSheet.Name Then
strAddress = rngInput.Address(False, False, xlA1, True)

I understand the InputBox method that came before this section of code, and
I understand that the False,False arguments, in the Address property, are
specifying a relative reference for both the rows and the columns, but I
don't understand what the whole If...Then statement about the Parent.Name is
doing. And I don't understand what the arguments xlA1 and True, in the
Address property, are telling VBA to do. (I suspect that the xlA1 is
stipulating the use of the Alpha-for-Columns and Numerals-for-Rows method of
cell referencing, as opposed to the R1C1 method, but I don't know what the
"True" argument is doing.)

And tell me how I can learn enough VBA to come up with such solutions on my
own?

And really, if you don't have time to answer these questions, don't worry
about it. You've already helped me immensely with this subprocedure.

Juan C.

You're welcome. Feel free to change "Test" to whatever you want. Thanks for
the feedback.

That did perfectly. Wish I understood everything that you did, and why. But
your Sub Test() did exactly the thing I wanted it to do. Thank you.
--
Juan C.

You could try:

Sub Test()
Dim rngInput As Range
Dim strAddress As String

On Error Resume Next
Set rngInput = Application.InputBox("Select Range", Type:=8)
On Error GoTo 0
If Not rngInput Is Nothing Then
If rngInput.Parent.Name <> ActiveSheet.Name Then
strAddress = rngInput.Address(False, False, xlA1, True)
Else: strAddress = rngInput.Address(False, False)
End If
ActiveCell.Formula = "=Subtotal(9," & _
strAddress & ")"
End If

End Sub

:

I frequently use this function: "=subtotal(9,[range address of cells to be
subtotaled])". This takes 13 keystrokes, not counting the range, which I
usually enter with the mouse.

What I need is a macro that will enter this: "=subtotal(9," and then
let me provide the range (with the mouse or the keyboard) and the closing
")".

I tried this subprocedure: ActiveCell.Formula = "=subtotal(9,". But that
produces an error.

The most workable solution that I've found is this: ActiveCell.Formula =
"=subtotal(9,x)"

Then, when this macro puts this into a cell, I edit the cell (F2), backspace
out the ")" and the "x", and then use the mouse to enter the actual range and
the ")". This is about 6 keystrokes (including the shortcut CTRL + S to run
the macro). That's better than 13 key strokes, but it would be even better
if the macro would start the function and just leave it for me to enter the
range.

Surely, there's a way to do it. But I don't know what it is.
Juan C.

 

[JMB]

You are correct about the xlA1. The parent of a range object is the
worksheet (so to get the workbook name it is rngInput.Parent.Parent.Name).
The last TRUE is to use the external range address if the range that is
selected is on a different sheet. So if you put the formula in Sheet1 and
select a range from sheet2, you'll get

=Subtotal(9, Sheet2!A1:A10)
and not
=Subtotal(9, A1:A10)

You could eliminate the IF and just include the sheet name all of the time,
but I don't like the extra clutter if it's not needed (and I can figure out a
way around it).

The macro recorder is a good tool to figure out how VBA refers to things, as
long as you recognize the recorder is pretty much limited to what you can
click on or type in Excel. VBA can do more than what can be recorded and the
recorder includes a lot of junk that is unnecessary (like scrolling and
selecting worksheets or ranges - you almost never need to select anything or
scroll).

I have some books by John Walkenbach (Programming and Worksheet Formulas),
that I like. Also have VBA Developers Handbook and Professional Excel
Development by Rob Bovey, Stephen Bullen, and John Green (and several others
at work that are geared more towards beginner level - you will need to decide
where you need to start). Browse the bookstore. Check out amazon. Check
mvps.org (excel link is on the right side) which has links to several excel
websites that have book recommendations, code samples, tutorials, etc.

xldynamic.com
mcgimpsey.com

and browsing throught the Q&A on this newsgroup.

Hey JMB, I've already changed it from Sub Test () to Sub subtotal(), saved it
to the Personal Macro Workbook (where it will be available to all workbooks),
given it a shortcut key, and used it three or four times. It's totally
slick, especially when I consider all the hours I spent in utter frustration
trying to figure out how to do it by myself.

If you've got time, and are willing, I wonder if you could explain what it
is you're doing with this section of the code:

If Not rngInput Is Nothing Then
If rngInput.Parent.Name <> ActiveSheet.Name Then
strAddress = rngInput.Address(False, False, xlA1, True)

I understand the InputBox method that came before this section of code, and
I understand that the False,False arguments, in the Address property, are
specifying a relative reference for both the rows and the columns, but I
don't understand what the whole If...Then statement about the Parent.Name is
doing. And I don't understand what the arguments xlA1 and True, in the
Address property, are telling VBA to do. (I suspect that the xlA1 is
stipulating the use of the Alpha-for-Columns and Numerals-for-Rows method of
cell referencing, as opposed to the R1C1 method, but I don't know what the
"True" argument is doing.)

And tell me how I can learn enough VBA to come up with such solutions on my
own?

And really, if you don't have time to answer these questions, don't worry
about it. You've already helped me immensely with this subprocedure.

Juan C.

You're welcome. Feel free to change "Test" to whatever you want. Thanks for
the feedback.

That did perfectly. Wish I understood everything that you did, and why. But
your Sub Test() did exactly the thing I wanted it to do. Thank you.
--
Juan C.


:

You could try:

Sub Test()
Dim rngInput As Range
Dim strAddress As String

On Error Resume Next
Set rngInput = Application.InputBox("Select Range", Type:=8)
On Error GoTo 0
If Not rngInput Is Nothing Then
If rngInput.Parent.Name <> ActiveSheet.Name Then
strAddress = rngInput.Address(False, False, xlA1, True)
Else: strAddress = rngInput.Address(False, False)
End If
ActiveCell.Formula = "=Subtotal(9," & _
strAddress & ")"
End If

End Sub

:

I frequently use this function: "=subtotal(9,[range address of cells to be
subtotaled])". This takes 13 keystrokes, not counting the range, which I
usually enter with the mouse.

What I need is a macro that will enter this: "=subtotal(9," and then
let me provide the range (with the mouse or the keyboard) and the closing
")".

I tried this subprocedure: ActiveCell.Formula = "=subtotal(9,". But that
produces an error.

The most workable solution that I've found is this: ActiveCell.Formula =
"=subtotal(9,x)"

Then, when this macro puts this into a cell, I edit the cell (F2), backspace
out the ")" and the "x", and then use the mouse to enter the actual range and
the ")". This is about 6 keystrokes (including the shortcut CTRL + S to run
the macro). That's better than 13 key strokes, but it would be even better
if the macro would start the function and just leave it for me to enter the
range.

Surely, there's a way to do it. But I don't know what it is.
Juan C.