VBA Summations

[John]

Hello,

I am trying to sum a variety of cells in MS Excel using
VBA. I want to have the ability to change the cells that I
sum. But I cannot use an autosum formula because the cells
are not next to where I want the summation. Additionally I
would like a Sum formula to be placed inside the cell and
not just a number.

I would like to use a range formula similar to

range(activecell.offset(0,-10),selection.end
(xltoright)).select

to select the area I want to SUM. Then sum that range.

Is it possible to use Summation formulas in VBA this way.
I have only been able to use them with specific cell
assignments?

Thanks

 

[William]

John

To place a total under the last row with data in Column E and sum from E2 to
that last row, you could use something like:-

Range("E" & Rows.Count).End(xlUp).Offset(1, 0).FormulaR1C1 =
"=SUM(R2C5:OFFSET(RC5,-1,0))"


--
XL2002
Regards

William

(e-mail address removed)

| Hello,
|
| I am trying to sum a variety of cells in MS Excel using
| VBA. I want to have the ability to change the cells that I
| sum. But I cannot use an autosum formula because the cells
| are not next to where I want the summation. Additionally I
| would like a Sum formula to be placed inside the cell and
| not just a number.
|
| I would like to use a range formula similar to
|
| range(activecell.offset(0,-10),selection.end
| (xltoright)).select
|
| to select the area I want to SUM. Then sum that range.
|
| Is it possible to use Summation formulas in VBA this way.
| I have only been able to use them with specific cell
| assignments?
|
| Thanks
|

 

[teatree]

you've got all the pieces it seems, just put them together...
use your range assignment to assign an address to a variable, and the
insert the variable into a formula

like so:

targetcell = "A1"


cell2 = range(activecell.offset(0,-10),selection.end
(xltoright)).Address

range(targetcell).formula = "=sum(a1:" & cell2 & ")

 

[Guest]

This looks great thanks -- just one more thing - is there
a way to do this without having the dollar signs show up
in the formula?

Thanks again

 

[William]

Hi

Sub test()
Range("E" & Rows.Count).End(xlUp).Offset(1, 0).FormulaR1C1 = _
"=SUM(R2C5:OFFSET(RC5,-1,0))"
Range("E" & Rows.Count).End(xlUp).Replace _
What:="$", Replacement:="", LookAt:=xlPart
End Sub

--
XL2002
Regards

William

(e-mail address removed)

| This looks great thanks -- just one more thing - is there
| a way to do this without having the dollar signs show up
| in the formula?
|
| Thanks again
|
|
|
|
|
| >-----Original Message-----
| >you've got all the pieces it seems, just put them
| together...
| >use your range assignment to assign an address to a
| variable, and then
| >insert the variable into a formula
| >
| >like so:
| >
| >targetcell = "A1"
| >
| >
| >cell2 = range(activecell.offset(0,-10),selection.end
| >(xltoright)).Address
| >
| >range(targetcell).formula = "=sum(a1:" & cell2 & ")"
| >
| >
| >---
| >Message posted
| >
| >.
| >