Sum cells above selected cell

[AndyM90]

Hi,

I am trying to create a macro to select the next blank cell in a column and sum the cells above it. However all the examples I can find on the internet dont seem to work.

The column is always column W, the row varies. So far I have the below which selects the cell I want the formula.

[COLOR=#333333]Range("W1").End(xldown).Offset(2,0).Select
[/COLOR]

now I want to sum the cells above this i.e. normally I could use the below if it is always a set range of rows. but it isn't as the amount of data varies daily.

ActiveCell.Formula = "=SUM(W1:W100)"

Any ideas how I can do this?

Thanks,

Andy

 

[Jonmo1]

Try

Range("W1").End(xldown).Offset(2,0).FormulaR1C1 = "=SUM(R1C:R[-1]C)"

 

[Darren Bartrup]

Try:

ActiveCell.FormulaR1C1 = "=SUM(R1C:R[-1]C)"

Is the same as =SUM(A$1:A10) if entered in A11.

R1C is Row 1, this column.
R[-1]C is this row -1, this column.