Imagine a new worksheet and in the range B2:B6 you have the values 1
3 4 5. If you highlight this range, provided you have selected "SUM
from the right click menu on the statusbar, the result Sum = 25 appear
in the status bar.

So, in VBA, how do I capture that result!? I want to be able to selec
my range - Range("B2:B6").select - and then capture that sum of th
values within that range but I can't work out how to do it.

Yes I could loop through all the cells within the range building up th
calc but I must be able to use the status bar Auto Calculate featur
somehow....

Many thanks guys

Ba

--
Message posted from http://www.ExcelForum.com

Your answer is in the formula property.

Example:
Range("A1").Formula = "=SUM(B2:B6)"

You can also concatinate this string together like:

Range("A1").Formula = "=SUM(" & firstcell & ":" & _
lastcell & ")"

Also, take a look at the formulaR1C1 property for doing
formulas positional to the cell your putting the formula
into.


-----Original Message-----
Imagine a new worksheet and in the range B2:B6 you have
the values 1 2
3 4 5. If you highlight this range, provided you have
selected "SUM"
from the right click menu on the statusbar, the result
Sum = 25 appears
in the status bar.

So, in VBA, how do I capture that result!? I want to be
able to select
my range - Range("B2:B6").select - and then capture
that sum of the
values within that range but I can't work out how to do
it.

Yes I could loop through all the cells within the range
building up the
calc but I must be able to use the status bar Auto
Calculate feature
somehow....

Many thanks guys

Baz


---
Message posted from http://www.ExcelForum.com/

.

Baz

in the immediate window:

?application.worksheetfunction.Sum(selection)

Regards

Trevor


"FastOneBaz " wrote in message
...
Imagine a new worksheet and in the range B2:B6 you have the values 1 2
3 4 5. If you highlight this range, provided you have selected "SUM"
from the right click menu on the statusbar, the result Sum = 25 appears
in the status bar.

So, in VBA, how do I capture that result!? I want to be able to select
my range - Range("B2:B6").select - and then capture that sum of the
values within that range but I can't work out how to do it.

Yes I could loop through all the cells within the range building up the
calc but I must be able to use the status bar Auto Calculate feature
somehow....

Many thanks guys

Baz


---
Message posted from http://www.ExcelForum.com/

I think you want

myVar = WorksheetFunction.SUM(Range("B2:B6"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"FastOneBaz " wrote in message
...
Imagine a new worksheet and in the range B2:B6 you have the values 1 2
3 4 5. If you highlight this range, provided you have selected "SUM"
from the right click menu on the statusbar, the result Sum = 25 appears
in the status bar.

So, in VBA, how do I capture that result!? I want to be able to select
my range - Range("B2:B6").select - and then capture that sum of the
values within that range but I can't work out how to do it.

Yes I could loop through all the cells within the range building up the
calc but I must be able to use the status bar Auto Calculate feature
somehow....

Many thanks guys

Baz


---
Message posted from http://www.ExcelForum.com/

Thanks, that works great. Excellent I'm well chuffed

--
Message posted from http://www.ExcelForum.com