Syntax question

[Stuart]

Some help, please:

ActiveCell.Offset(0, 1).Formula = _
"=Sum(G4: ...........G.?....)"

where G? can be defined as the cell:
ActiveCell.Offset(-2, 1)

As I get older, I forget even the simplest of things!

Regards.

 

[Dianne]

ActiveCell.Offset(0, 1).Formula = _
"=Sum($G$4:" & ActiveCell.Offset(-2, 1).Address & ")"

 

[Tom Ogilvy]

Some help, please:

ActiveCell.Offset(0, 1).Formula = _
"=Sum(G4:G" & ActiveCell.Offset(-2,1) & ")"

if you want it hard coded. If you want your formula to dynamically refer to
this cell

"=Sum(Indirect(""G4:G"" & " & ActiveCell.Offset(-2,1).Address(0,0) & "))"


? "=Sum(Indirect(""G4:G"" & " & ActiveCell.Offset(-2,1).Address(0,0) &
"))"
=Sum(Indirect("G4:G" & E97))

so if E97 contained 100 then the sum would sum G4:G100

 

[Stuart]

Many thanks to you both.

Forgot the need for 'Address'

Regards.

ActiveCell.Offset(0, 1).Formula = _
"=Sum($G$4:" & ActiveCell.Offset(-2, 1).Address & ")"