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.


"JMB" wrote:

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

"Juan" wrote:

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.


"JMB" wrote:

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" wrote:

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 subprocedu 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.