Need to Sum all values above

[mkerstei]

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 = *****

[Chip Pearson]

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"
<mkerstei.28y8lm_1149544501.5514@excelforum-nospam.com> wrote in
message
news:mkerstei.28y8lm_1149544501.5514@excelforum-nospam.com...
>
> 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
>

[Don Guillett]

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
dguillett1@austin.rr.com
"mkerstei" <mkerstei.28y8lm_1149544501.5514@excelforum-nospam.com> wrote in
message news:mkerstei.28y8lm_1149544501.5514@excelforum-nospam.com...
>
> 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]

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

[mkerstei]

Thanks for everyones help!