macro to sum an unknown range
 

I have a worksheet that I import data into weekly. I have a macro that
formats all the data on separate sheets, etc. I'm trying to add a
macro that will sum the totals with a beginning range that is always
the same but the bottom of the range is always different. I use a
relative macro that takes me to the last cell with data to actually put
the total in. Now, how do i get it to total from that cell always up
to row 2?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

macro to sum an unknown range
 

To do with a macro, use this. This is for col D. Change to suit

Sub sumem()
x = Cells(65536, "d").End(xlUp).Row
MsgBox Application.Sum(Range(Cells(2, 4), Cells(x, 4)))
End Sub

"susiel" wrote in message
...
I have a worksheet that I import data into weekly. I have a macro that
formats all the data on separate sheets, etc. I'm trying to add a
macro that will sum the totals with a beginning range that is always
the same but the bottom of the range is always different. I use a
relative macro that takes me to the last cell with data to actually put
the total in. Now, how do i get it to total from that cell always up
to row 2?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

macro to sum an unknown range
 

To put at the bottom of the column
Sub sumem()
x = Cells(65536, "d").End(xlUp).Row
cells(x+1,"d") = Application.Sum(Range(Cells(2, 4), Cells(x, 4)))
End Sub



"Don Guillett" wrote in message
...
To do with a macro, use this. This is for col D. Change to suit

Sub sumem()
x = Cells(65536, "d").End(xlUp).Row
MsgBox Application.Sum(Range(Cells(2, 4), Cells(x, 4)))
End Sub

"susiel" wrote in message
...
I have a worksheet that I import data into weekly. I have a macro that
formats all the data on separate sheets, etc. I'm trying to add a
macro that will sum the totals with a beginning range that is always
the same but the bottom of the range is always different. I use a
relative macro that takes me to the last cell with data to actually put
the total in. Now, how do i get it to total from that cell always up
to row 2?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

macro to sum an unknown range
 

Susie,

To add the SUM formula, use a macro like that below.

HTH,
Bernie

Sub SumAtBottomOfCurrentColumn()
Dim myCell As Range
Set myCell = Cells(65536, ActiveCell.Column).End(xlUp)(2)
With myCell
.Formula = "=SUM(" & _
Range(.Offset(-1, 0), _
Cells(2, .Column)).Address(False, False) & ")"
End With
End Sub


"susiel" wrote in message
...
I have a worksheet that I import data into weekly. I have a macro
that
formats all the data on separate sheets, etc. I'm trying to add a
macro that will sum the totals with a beginning range that is always
the same but the bottom of the range is always different. I use a
relative macro that takes me to the last cell with data to actually
put
the total in. Now, how do i get it to total from that cell always
up
to row 2?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

macro to sum an unknown range
 

Sub Tester1()
Dim rng As Range
Set rng = Cells(Rows.Count, ActiveCell.Column).End(xlUp)(2)
rng.FormulaR1C1 = "=Sum(R2C:R[-1]C)"
End Sub

Regards,
Tom Ogilvy


"susiel" wrote in message
...
I have a worksheet that I import data into weekly. I have a macro that
formats all the data on separate sheets, etc. I'm trying to add a
macro that will sum the totals with a beginning range that is always
the same but the bottom of the range is always different. I use a
relative macro that takes me to the last cell with data to actually put
the total in. Now, how do i get it to total from that cell always up
to row 2?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/