Getting worksheet.function.sum to reference correctly

sagain2k

Board Regular
Joined
Sep 8, 2002
Messages
94
I'm trying to sum up a range that is selected using an offset variable...tried this:

ActiveCell.Value = Application.WorksheetFunction.Sum(ActiveCell.Offset(0, -ColCalcNum).Address & ":" & ActiveCell.Offset(0, -1).Address)

Can't quite get the correct Syntax for this, or for the "Activecell.Formula = ..." method. Any suggestions?
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
The help file is incorrect
You will need to use an object range for this

eg
<pre/>
Dim MyRg as Range

Set MyRg = Range(ActiveCell.Offset(0, -ColCalcNum).Address & ":" & ActiveCell.Offset(0, -1).Address)
ActiveCell.Value = Application.WorksheetFunction.Sum(MyRg)
MsgBox ActiveCell.Value

</pre>
 

Watch MrExcel Video

Forum statistics

Threads
1,118,917
Messages
5,575,027
Members
412,635
Latest member
Arren
Top