Need to Sum all values above
 

I need to write a macro that will sum all of the values above the
selected cell. Rows above cell are variable, so it has to be flexible.
Below is my code. I believe the sum formula will be where the *****
are. Any help would be appreciated. Thanks.

Range("E1").Select
Selection.End(xlDown).Select
ReportLastRow = ActiveCell.Row
ActiveCell.Offset(rowOffset:=1, columnoffset:=0).Activate
ActiveCell.FormulaR1C1 = _
"Total"
ActiveCell.Offset(rowOffset:=0, columnoffset:=1).Activate
ActiveCell.FormulaR1C1 = *****


--
mkerstei
------------------------------------------------------------------------
mkerstei's Profile: http://www.excelforum.com/member.php...o&userid=25688
View this thread: http://www.excelforum.com/showthread...hreadid=548761

Need to Sum all values above
 

Assuming that the ActiveCell is at the blank cell following the
column of numbers to sum, use

Dim Addr As String
Dim Rng As Range
Set Rng = ActiveCell.End(xlUp).End(xlUp)
Addr = Rng.Address
ActiveCell.Formula = "=SUM(" & Addr & ":" & ActiveCell(0,
1).Address & ")"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"mkerstei"
wrote in
message
...

I need to write a macro that will sum all of the values above
the
selected cell. Rows above cell are variable, so it has to be
flexible.
Below is my code. I believe the sum formula will be where the
*****
are. Any help would be appreciated. Thanks.

Range("E1").Select
Selection.End(xlDown).Select
ReportLastRow = ActiveCell.Row
ActiveCell.Offset(rowOffset:=1, columnoffset:=0).Activate
ActiveCell.FormulaR1C1 = _
"Total"
ActiveCell.Offset(rowOffset:=0, columnoffset:=1).Activate
ActiveCell.FormulaR1C1 = *****


--
mkerstei
------------------------------------------------------------------------
mkerstei's Profile:
http://www.excelforum.com/member.php...o&userid=25688
View this thread:
http://www.excelforum.com/showthread...hreadid=548761

Need to Sum all values above
 

this question must be going around today. Homework?
active column and active row

Sub sumabove()
mc = ActiveCell.Column
MsgBox Application.Sum _
(Range(Cells(1, mc), Cells(ActiveCell.Row - 1, mc)))
End Sub


--
Don Guillett
SalesAid Software

"mkerstei" wrote in
message ...

I need to write a macro that will sum all of the values above the
selected cell. Rows above cell are variable, so it has to be flexible.
Below is my code. I believe the sum formula will be where the *****
are. Any help would be appreciated. Thanks.

Range("E1").Select
Selection.End(xlDown).Select
ReportLastRow = ActiveCell.Row
ActiveCell.Offset(rowOffset:=1, columnoffset:=0).Activate
ActiveCell.FormulaR1C1 = _
"Total"
ActiveCell.Offset(rowOffset:=0, columnoffset:=1).Activate
ActiveCell.FormulaR1C1 = *****


--
mkerstei
------------------------------------------------------------------------
mkerstei's Profile:
http://www.excelforum.com/member.php...o&userid=25688
View this thread: http://www.excelforum.com/showthread...hreadid=548761

Need to Sum all values above
 

ActiveCell.FormulaR1C1 = "=sum(r1c5:rc[-1])"

===
Or drop the selecting:

Dim myCell As Range
With ActiveSheet
Set myCell = .Range("e1").End(xlDown).Offset(1, 0)
End With
myCell.Value = "Total"
myCell.Offset(0, 1).FormulaR1C1 = "=sum(r1c5:rc[-1])"

mkerstei wrote:

I need to write a macro that will sum all of the values above the
selected cell. Rows above cell are variable, so it has to be flexible.
Below is my code. I believe the sum formula will be where the *****
are. Any help would be appreciated. Thanks.

Range("E1").Select
Selection.End(xlDown).Select
ReportLastRow = ActiveCell.Row
ActiveCell.Offset(rowOffset:=1, columnoffset:=0).Activate
ActiveCell.FormulaR1C1 = _
"Total"
ActiveCell.Offset(rowOffset:=0, columnoffset:=1).Activate
ActiveCell.FormulaR1C1 = *****

--
mkerstei
------------------------------------------------------------------------
mkerstei's Profile: http://www.excelforum.com/member.php...o&userid=25688
View this thread: http://www.excelforum.com/showthread...hreadid=548761

--

Dave Peterson

Need to Sum all values above
 

Thanks for everyones help!


--
mkerstei
------------------------------------------------------------------------
mkerstei's Profile: http://www.excelforum.com/member.php...o&userid=25688
View this thread: http://www.excelforum.com/showthread...hreadid=548761