Copying formula to the bottom of a range

[Guest]

I'm trying to create a macro that copies a formula in a column down to the
bottom of a range.

For example, I have data in columns A - L. In column M, I want to create a
formula and copy it down to the bottom of my data. The only problem is my
data is dynamic. One day it might end on row 1038, another day it might end
on row 1152.

Anyone have VBA code that can help me do this?

 

[Tom Ogilvy]

set rng = range(cells(1,"L"),cells(rows.count,"L").End(xlup))
rng.offset(0,1).Formula = "=Sum(A1:C1)"

make the formula relative to the first cell in your range and use absolute
and relative addressing as appropriate.

if you already have it in M1 for example, then it would be
set rng = range(cells(1,"L"),cells(rows.count,"L").End(xlup))
rng.offset(0,1).Formula = Range("M1").formula

 

[Guest]

thanks Tom, that worked like a charm.

set rng = range(cells(1,"L"),cells(rows.count,"L").End(xlup))
rng.offset(0,1).Formula = "=Sum(A1:C1)"

make the formula relative to the first cell in your range and use absolute
and relative addressing as appropriate.

if you already have it in M1 for example, then it would be
set rng = range(cells(1,"L"),cells(rows.count,"L").End(xlup))
rng.offset(0,1).Formula = Range("M1").formula