need help creating "AutoSubtotal" toolbar button

P

Paul Simon

Similar to how the AutoSum toolbar button enters a =SUM formula into a
cell, I'm trying to create an "AutoSubtotal" button that would enter a
=SUBTOTAL formula into a cell. (I'm not trying to do Data>Subtotal. I
just want to enter a =SUBTOTAL formula into the cell.)

I thought this would work, but it doesn't. Any help would be greatly
appreciated.


Sub AutoSubtotal()
If ActiveCell.Offset(-1, 0).Formula = "" Then End
Else
ActiveCell.Formula = "=subtotal(9," & _
Range(ActiveCell.Offset(-1, 0).End(xlUp), _
ActiveCell.Offset(-1, 0)) & ")"
End If
End Sub


(I need the cell to actually contain a formula, so I can't use
WorksheetFunction.)

Many thanks,
Paul
 
S

steve

Paul,

Try this version:

Sub AutoSubtotal()
If ActiveCell.Offset(-1, 0).Formula = "" Then
Else
ActiveCell.Formula = "=subtotal(9," & _
ActiveCell.Offset(-1, 0).End(xlUp).Address _
& ":" & ActiveCell.Offset(-1, 0).Address & ")"
End If
End Sub
 
P

Paul Simon

Tom and Steve,

Both solutions work perfectly. Thank you both for responding to my
question and for taking the time to work out the code for me - I
appreciate it very much.

Many thanks,
Paul
 
S

steve

Paul,

Glad we could be of help!

--
sb
Paul Simon said:
Tom and Steve,

Both solutions work perfectly. Thank you both for responding to my
question and for taking the time to work out the code for me - I
appreciate it very much.

Many thanks,
Paul


"steve" <[email protected]> wrote in message
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top