Copying formula to the bottom of a range

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?

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

--
Regards,
Tom Ogilvy



"jeremy nickels" <jeremy [email protected]> wrote in message
news:[email protected]...
> 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?

thanks Tom, that worked like a charm.

"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "jeremy nickels" <jeremy [email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>