need dynamic range

[kendel]

Hello, I have the feeling many here will find this very basic stuff. When the vba code inserts this formula into cell S22 , it works perfectly, but when the code inserts it into S33 it sums ac22:ad22 again, naturally. With what can I replace the '22' to make the range totally independant of where on the worksheet it is, so it will sum whatever row it's in? As you see everything is relative.

Selection.select 'starts like this

'other stuff

ActiveCell.Offset(0, -2).Select
Selection.NumberFormat = "0"
Selection.formula= "=if((sum(ac22:ad22))>0,sum(ac22:ad22),"""")"
ActiveCell.Offset(0, 10).Select

Thanks in advance
Kendel
excel 2007 & 2010

 

[VoG]

Possibly

Selection.Formula = "=if((sum(ac" & ActiveCell.Row & ":ad" & ActiveCell.Row & "))>0,sum(ac" & ActiveCell.Row & ":ad" & ActiveCell.Row & "),"""")"

 

[Weaver]

Maybe with R1C1 notation:

with activecell.Offset(0, -2)
    .NumberFormat = "0"
    .formulaR1C1 = "=if((sum(rc29:rc30))>0,sum(rc29:rc30),"""")"
end with

 

[kendel]

Thank you, works just like I need