insert formula in last cell
 

Hi - I would like to be able to subtotal a column of numbers using the
following formula:

=subtotal(9, C2:?) where ? is the last cell that contains data. I would like to
insert the formula in the cell below the last row of data.

The data to be summed are in reports that I download daily and where the number
of rows that contain data will vary. I've tried to figure out how to do this
with a macro, but I'm stumped. Any help is appreciated.

Cheers,

Angela

insert formula in last cell
 

Angela, This should do it for you:

Sub subtotal()
Dim end_data As Long
end_data = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
Range("C" & end_data + 1).Formula = "=SUBTOTAL(9,C2:C" & end_data & ")"
End Sub

Mike F
"J_Gold" wrote in message
...
Hi - I would like to be able to subtotal a column of numbers using the
following formula:

=subtotal(9, C2:?) where ? is the last cell that contains data. I would
like to insert the formula in the cell below the last row of data.

The data to be summed are in reports that I download daily and where the
number of rows that contain data will vary. I've tried to figure out how
to do this with a macro, but I'm stumped. Any help is appreciated.

Cheers,

Angela

insert formula in last cell
 

One way:

Range("C" & Rows.Count).End(xlUp).FormulaR1C1 = _
"=SUBTOTAL(9, R2C:R[-1]C)"

In article ,
"J_Gold" wrote:

Hi - I would like to be able to subtotal a column of numbers using the
following formula:

=subtotal(9, C2:?) where ? is the last cell that contains data. I would like
to
insert the formula in the cell below the last row of data.

The data to be summed are in reports that I download daily and where the
number
of rows that contain data will vary. I've tried to figure out how to do this
with a macro, but I'm stumped. Any help is appreciated.

Cheers,

Angela

insert formula in last cell
 

Hi Mike - thanks, works great!

Cheers,

Angela
"Mike Fogleman" wrote in message
...
Angela, This should do it for you:

Sub subtotal()
Dim end_data As Long
end_data = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
Range("C" & end_data + 1).Formula = "=SUBTOTAL(9,C2:C" & end_data & ")"
End Sub

Mike F
"J_Gold" wrote in message
...
Hi - I would like to be able to subtotal a column of numbers using the
following formula:

=subtotal(9, C2:?) where ? is the last cell that contains data. I would like
to insert the formula in the cell below the last row of data.

The data to be summed are in reports that I download daily and where the
number of rows that contain data will vary. I've tried to figure out how to
do this with a macro, but I'm stumped. Any help is appreciated.

Cheers,

Angela

insert formula in last cell
 

Hi Angela,
Implementing your formula would be half of the solution, so if you
would like to see how to invoke a subroutine to place a SUBTOTAL
at the end of the current column on a rightclick see
macro endtotal_sub implemented into your rightclick (context) menu in
Right Click Menus (Context Menus) in Excel
http://www.mvps.org/dmcritchie/excel/rightclick.htm

The macro itself would be

Sub EndTotal_sub()
Dim end_data As Long 'dmcritchie RClick 2005-05-28
Range(ActiveCell.Address, _
Cells(Rows.Count, ActiveCell.Column).End(xlUp).Address).Select
end_data = ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
ActiveSheet.Cells(end_data + 1, ActiveCell.Column).Formula = _
"=SUBTOTAL(9," & ActiveSheet.Cells(2, ActiveCell.Column).Address(1, 0) _
& ":OFFSET(" & ActiveSheet.Cells(end_data + 1, _
ActiveCell.Column).Address(0, 0) & ",-1,0))"
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"J_Gold" wrote l...
Hi Mike - thanks, works great!

Cheers,

Angela
"Mike Fogleman" wrote in message
...
Angela, This should do it for you: [clipped]

insert formula in last cell
 

Hi David - Thanks! hadn't even thought of taking it that far - perfect
solution.

Cheers,

Angela


"David McRitchie" wrote in message
...
Hi Angela,
Implementing your formula would be half of the solution, so if you
would like to see how to invoke a subroutine to place a SUBTOTAL
at the end of the current column on a rightclick see
macro endtotal_sub implemented into your rightclick (context) menu in
Right Click Menus (Context Menus) in Excel
http://www.mvps.org/dmcritchie/excel/rightclick.htm

The macro itself would be

Sub EndTotal_sub()
Dim end_data As Long 'dmcritchie RClick 2005-05-28
Range(ActiveCell.Address, _
Cells(Rows.Count, ActiveCell.Column).End(xlUp).Address).Select
end_data = ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
ActiveSheet.Cells(end_data + 1, ActiveCell.Column).Formula = _
"=SUBTOTAL(9," & ActiveSheet.Cells(2, ActiveCell.Column).Address(1, 0) _
& ":OFFSET(" & ActiveSheet.Cells(end_data + 1, _
ActiveCell.Column).Address(0, 0) & ",-1,0))"
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"J_Gold" wrote l...
Hi Mike - thanks, works great!

Cheers,

Angela
"Mike Fogleman" wrote in message
...
Angela, This should do it for you: [clipped]

insert formula in last cell
 

Hi Angela,
Glad to add something additional to help with Mike's solution.
I'm kind of partial to context menus as you might notice from
the related area at the bottom of that page for other applications
as well.
http://www.mvps.org/dmcritchie/excel/rightclick.htm

The reason for use of OFFSET is so that you can add or
delelet rows immediately before the total.
http://www.mvps.org/dmcritchie/excel/offset.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"J_Gold" wrote...
Hi David - Thanks! hadn't even thought of taking it that far - perfect
solution. ---Cheers, Angela