Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to open the Subtotal function
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to open the Subtotal function
Did you try selecting the region and clicking the sigma icon (AutoSum)?
The sigma icon looks like this: ----- \ \ / / ----- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to open the Subtotal function
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. " wrote: Did you try selecting the region and clicking the sigma icon (AutoSum)? The sigma icon looks like this: ----- \ \ / / ----- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to open the Subtotal function
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. -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Juan" wrote: 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. " wrote: Did you try selecting the region and clicking the sigma icon (AutoSum)? The sigma icon looks like this: ----- \ \ / / ----- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to open the Subtotal function
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to open the Subtotal function
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to open the Subtotal function
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to open the Subtotal function
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to open the Subtotal function
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort macro, subtotal and add lines after subtotal | Excel Discussion (Misc queries) | |||
macro excel subtotal in subtotal | Excel Discussion (Misc queries) | |||
OPEN.TEXT function in Excel 4.0 macro | Excel Programming | |||
Subtotal another open sheet in VBA | Excel Programming | |||
inserting subtotal() function in spreadsheet via Visual Basic/macro | Excel Programming |