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

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

--
sb
"Paul Simon" wrote in message
m...
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 DataSubtotal. 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

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" wrote in message ...
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

--
sb
"Paul Simon" wrote in message
m...
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 DataSubtotal. 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

Paul,

Glad we could be of help!

--
sb
"Paul Simon" wrote in message
om...
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" wrote in message
...
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

--
sb
"Paul Simon" wrote in message
m...
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 DataSubtotal. 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