From VB code I want to write a formula into the selected cell that will
add up the 10 cells to the right of the selected cell. I do not know the
location of the selected cell so I want some form of relative sum() formula.

ie =sum('from here', +1, +10) along the row.

I bet it is very simple but I cannot find anything in the help. All the
ones I can find expect you to know where you are starting from!

Thanks
Brian

myVar = Application.SUM(Activecell.Resize(,10))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ashnook" <brian@atashnookcdtfreeservedotcodotuk wrote in message
. com...

From VB code I want to write a formula into the selected cell that will
add up the 10 cells to the right of the selected cell. I do not know the
location of the selected cell so I want some form of relative sum()
formula.

ie =sum('from here', +1, +10) along the row.

I bet it is very simple but I cannot find anything in the help. All the
ones I can find expect you to know where you are starting from!

Thanks
Brian

Bob Phillips wrote:
myVar = Application.SUM(Activecell.Resize(,10))


Hi BOB, thanks for the swift reply, this gives me a #NAME? result? I
have tried other number and combination with no success. mu selected
cell in this try is B5, but it could be anything.

Brian

Brian,

Bob's formula is not only elegant, but it works for me :

Sub Macro1()
ActiveCell.Value = Application.Sum(ActiveCell.Resize(, 11))
End Sub

HTH
Cheers
Carim

Carim wrote:
Brian,

Bob's formula is not only elegant, but it works for me :

Sub Macro1()
ActiveCell.Value = Application.Sum(ActiveCell.Resize(, 11))
End Sub

HTH
Cheers
Carim

I may be misleading you, I want to write the formule in to the cell not
the result of the sum. This is so that whenever changes are made in the
10 right cells to sum in the selected cell changes accordingly.

The Vb code has to write the formula into the selected cell.

Brian

Brian,

I do not understand your requirement :

1. Either you want the formula in your cell, then the code is :
Sub Macro1()
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[10])"
End Sub
2. Or you want the sum itself to appear, then the code is :
Sub Macro1()
ActiveCell.Value = Application.Sum(ActiveCell.Resize(, 11))
End Sub

Hope this clarifies
Cheers
Carim

Hi Brian,

Something along this line :

ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[10])"

HTH
Cheers
Carim

Carim wrote:
Hi Brian,

Something along this line :

ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[10])"

HTH
Cheers
Carim

Thanks Carim for the swift reply, this solution means (I think) that I
have to turn on RC referencing for the whole sheet and I don't want to
do that if possible.

Brian

Try it. You'll be pleasantly surprised how smart VBA and Excel work together.

Ashnook wrote:

Carim wrote:
Hi Brian,

Something along this line :

ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[10])"

HTH
Cheers
Carim

Thanks Carim for the swift reply, this solution means (I think) that I
have to turn on RC referencing for the whole sheet and I don't want to
do that if possible.

Brian

--

Dave Peterson

Dave Peterson wrote:
Try it. You'll be pleasantly surprised how smart VBA and Excel work together.

Ashnook wrote:
Carim wrote:
Hi Brian,

Something along this line :

ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[10])"

HTH
Cheers
Carim

Thanks Carim for the swift reply, this solution means (I think) that I
have to turn on RC referencing for the whole sheet and I don't want to
do that if possible.

Brian

I may be misleading you, I want to write the formula in to the cell not
the result of the sum. This is so that whenever changes are made in the
10 right cells to sum in the selected cell changes accordingly.

The Vb code has to write the formula into the selected cell. When you
re-select the originally selected cell the =bar should show and =formula
not the result of the sum.

Brian

Give it a try.



Ashnook wrote:

Dave Peterson wrote:
Try it. You'll be pleasantly surprised how smart VBA and Excel work together.

Ashnook wrote:
Carim wrote:
Hi Brian,

Something along this line :

ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[10])"

HTH
Cheers
Carim

Thanks Carim for the swift reply, this solution means (I think) that I
have to turn on RC referencing for the whole sheet and I don't want to
do that if possible.

Brian

I may be misleading you, I want to write the formula in to the cell not
the result of the sum. This is so that whenever changes are made in the
10 right cells to sum in the selected cell changes accordingly.

The Vb code has to write the formula into the selected cell. When you
re-select the originally selected cell the =bar should show and =formula
not the result of the sum.

Brian

--

Dave Peterson