Hi,

Simple formula to insert a SUM.

Range("G22").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(G5:R[-2]C)"

But it returns in Excel......=SUM('G5':G124)
which doesn't work.

Any idea?

Thanks John

Hi John

I can't see why you are selecting G22, but wanting the formula to run
from G5.

Try the following

Sub test4()
Dim lr As Long
Range("G5").Select
lr = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
Cells(Rows.Count, ActiveCell.Column).End(xlUp)(3).Select
ActiveCell.Formula = "=Sum(" & "G5:G" & lr & ")"

End Sub

Amend to suit
--
Regards

Roger Govier


"John" wrote in message
...
Hi,

Simple formula to insert a SUM.

Range("G22").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(G5:R[-2]C)"

But it returns in Excel......=SUM('G5':G124)
which doesn't work.

Any idea?

Thanks John

Can't see why i delected G22 either. Your formula worked spot on though.

Cheers Roger

"Roger Govier" wrote:

Hi John

I can't see why you are selecting G22, but wanting the formula to run
from G5.

Try the following

Sub test4()
Dim lr As Long
Range("G5").Select
lr = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
Cells(Rows.Count, ActiveCell.Column).End(xlUp)(3).Select
ActiveCell.Formula = "=Sum(" & "G5:G" & lr & ")"

End Sub

Amend to suit
--
Regards

Roger Govier


"John" wrote in message
...
Hi,

Simple formula to insert a SUM.

Range("G22").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(G5:R[-2]C)"

But it returns in Excel......=SUM('G5':G124)
which doesn't work.

Any idea?

Thanks John

Hi John

Can't see why i delected G22 either.
Glad it just wasn't me!!!

Thanks for the feedback, pleased you got it to work

--
Regards

Roger Govier


"John" wrote in message
...
Can't see why i delected G22 either. Your formula worked spot on
though.

Cheers Roger

"Roger Govier" wrote:

Hi John

I can't see why you are selecting G22, but wanting the formula to run
from G5.

Try the following

Sub test4()
Dim lr As Long
Range("G5").Select
lr = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
Cells(Rows.Count, ActiveCell.Column).End(xlUp)(3).Select
ActiveCell.Formula = "=Sum(" & "G5:G" & lr & ")"

End Sub

Amend to suit
--
Regards

Roger Govier


"John" wrote in message
...
Hi,

Simple formula to insert a SUM.

Range("G22").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(G5:R[-2]C)"

But it returns in Excel......=SUM('G5':G124)
which doesn't work.

Any idea?

Thanks John

with a macro you have to use the row/column method of identifying a
cell in a formula - when i record =sum(blah:blah), this is what you get
in the VBA editor:

ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-2]C)"

(don't use this specific one - it doesn't sum as many rows as you
wanted).
susan

To the best of my knowledge you can't combine R1C1 and A1 reference
styles. Try This:
Range("G22").End(xlDown).Offset(2).FormulaR1C1 = "=SUM(R5C:R[-2]C)"

HTH

Charles Chickering
John wrote:
Hi,

Simple formula to insert a SUM.

Range("G22").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(G5:R[-2]C)"

But it returns in Excel......=SUM('G5':G124)
which doesn't work.

Any idea?

Thanks John