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.

"Juan" wrote:

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.