A little help with cell reference please

[Ashnook]

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]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Carim]

Hi Brian,

Something along this line :

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

HTH
Cheers
Carim

 

[Ashnook]

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

 

[Ashnook]

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

 

[Carim]

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

 

[Dave Peterson]

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

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

 

[Ashnook]

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

 

[Ashnook]

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

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

 

[Carim]

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

 

[Dave Peterson]

Give it a try.

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

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

 

[Ashnook]

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

Aghhh!! it's me (as usual)

I want 1. so I typed out what you have written into the selected cell,
which complains.

So I added the code you suggested into my VB code and it works!!

Thanks a lot!

 

[Ashnook]

Give it a try.

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

Aghhh!! it's me (as usual)

to try it I typed out what you have written into the selected cell,
which complains.

So I added the code you suggested into my VB code and it works!!

Thanks a lot!

 

[Carim]

Glad you have managed to fix your problem ...

Cheers

Carim

 

[Ashnook]

Glad you have managed to fix your problem ...

Cheers

Carim

Yes thanks, the problem was all me!