Activecell.FormulaR1C1 = "=SUM(R1C:R[-1]C)"
*** ANY TAX ADVICE IN THIS COMMUNICATION IS NOT INTENDED
OR WRITTEN BY KPMG TO BE USED, AND CANNOT BE USED, BY A
CLIENT OR ANY OTHER PERSON OR ENTITY FOR THE PURPOSE OF (i)
AVOIDING PENALTIES THAT MAY BE IMPOSED ON ANY TAXPAYER OR
(ii) PROMOTING, MARKETING OR RECOMMENDING TO ANOTHER PARTY
ANY MATTERS ADDRESSED HEREIN. ***
Any advice in this communication is limited to the
conclusions specifically set forth herein and is based on
the completeness and accuracy of the stated facts,
assumptions and/or representations included. In rendering
our advice, we may consider tax authorities that are subject
to change, retroactively and/or prospectively, and any such
changes could affect the validity of our advice. We will not
update our advice for subsequent changes or modifications to
the law and regulations, or to the judicial and
administrative interpretations thereof.
The advice or other information in this document was
prepared for the sole benefit of KPMG's client and may not
be relied upon by any other person or organization. KPMG
accepts no responsibility or liability in respect of this
document to any person or organization other than KPMG's
client.
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-G PEACH.EASE.LSOFT.COM]
On Behalf Of Robert Rafferty
Sent: Wednesday, January 31, 2007 3:39 PM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Sum range above active cell in VBA
Bob:
The works perfectly. Thanks.
I know I should have thought about this before (somehow
seeing the
solution
generates another thought)
Is there a way to have a "live" formula in the
active cell, very much
like regular
Excel as if I did the autosum manually. Like I said, the
solution
offered does
the trick, but my audience is used to seeing the =sum
(Q2:Qn).
Thanks
On Wed, 31 Jan 2007 11:45:21 -0800, Bob Umlas
<bobumlasYAHOO.COM>
wrote:
>activecell.value=application.sum(cells(1,activecell.colu
mn).resize
(activecell.row-1))
Bob Umlas, Excel MVP
----- Original Message ----
From: "Rafferty, Robert" <Robert.RaffertySAVVIS.NET>
To: EXCEL-GPEACH.EASE.LSOFT.COM
Sent: Wednesday, January 31, 2007 2:36:18 PM
Subject: Sum range above active cell in VBA
Hello All
I want to sum the range above my active cell using vba.
It's a no-brainer every day thing in normal excel, but I
can't figure
out how to do this simple thing in vba at this point in
time.
I have 5 worksheets that I want sum each day.
The active cell varies as does the range to sum above.
I cobbled the following code but does not do the task.
What am I missing?
Sub LastCellInColumnQ()
Dim R As Long, C As Integer
R = ActiveCell.Row
C = ActiveCell.Column
Range("Q65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Application.Sum(Range(Cells(R, 2),
Cells(R, C)))
End Sub
Any help would be most appreciated.
Robb
Robb Rafferty
SAVVIS
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
____________________________________________________________
______
__________________
Don't get soaked. Take a quick peak at the forecast
with the Yahoo! Search weather shortcut.
http://tools.search.yahoo.com/shortcuts/#loc_weather
>
>--------------------------------------------------------
---------------
---
>The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
>international's LISTSERV(R) software. For
subscription/signoff info
>and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
> COPYRIGHT INFO:
>http://peach.ease.lsoft.com/scripts/wa.
exe?SHOWTPL=COPYRIGHT&L=EXCEL-
G
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
<html>
<body>
<p>***************************************************
********************</p>
<p>The information in this email is confidential and
may be legally privileged.
It is intended solely for the addressee. Access to this
email by anyone else is
unauthorized. If you are not the intended recipient, any
disclosure, copying,
distribution or any action taken or omitted to be taken in
reliance on it, is
prohibited and may be unlawful. When addressed to our
clients any opinions or
advice contained in this email are subject to the terms and
conditions
expressed in the governing KPMG client engagement
letter.</p>
<p>***************************************************
********************</p>
</body>
</html>
------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
|