VBA inserting sum formula with unknown end point

[aussieembassy]

Im trying to write some script that basically inserts "=sum(O7:O38" into a cell however O38 is not fixed.
I could write the script to actually do the sum however the numbers within that range are often changed and rather than re running the script it would be instant to see the change.

Any ideas on this?
cheers

 

[Sal Paradise]

Rather than VBA, you can do this with formulas.

For instance, you create a named range (let's call it ORange) using this formula:
=OFFSET(Sheet1!$O$7,0,0,COUNTA(Sheet1!$O:$O)-COUNTA(Sheet1!$O$1:$O$6),1)

That will automatically change the range to include all the cells from O7 on down (assuming no blanks in the range).

Then you can create a formula that just says:
=SUM(ORange)

 

[drsarao]

To allow for blanks in the range:

=SUM(OFFSET(Sheet1!$O$7,0,0,MATCH(9.999E+307,Sheet1!$O:$O)-6,1))