Autofill formula to last row with data when rows will be dynamic

A,

Replace

ActiveCell.FormulaR1C1 = "=RC[-1]*RC[6]/12"

with

Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)) _
.FormulaR1C1 = "=RC[-1]*RC[6]/12"

HTH,
Bernie
MS Excel MVP


"A Waller" <AWaller@discussions.microsoft.com> wrote in message
news:72C95C96-3CA1-4CBA-B470-4581FCD4B42A@microsoft.com...
> I am writing a macro. It's more complicated than I originally thought.
What I
> need to do is autofill a formula in a column to the last row with data.
The
> problem is the rows are dynamic. They start in a different place each time
> and end in a different place. The range size will vary every time the
macro
> is used. Once the formula has calculated the neccessary numbers I want to
> copy and paste the information back to another column. My problem is
telling
> excel which function I want to perform when the calculations will be made
on
> a moving target. I have the macro working to the first cell where the
formula
> is entered. Now I need to copy the formula to the last row that contains
> information the formula can use. Please help. I have posted this
previously
> but it was days ago and still no answer. Here's a sample of my macro. This
is
> only a small part.
>
> Sheets("BOMSized").Select
> Cells.Find(What:="RIM", After:=ActiveCell, LookIn:=xlFormulas _
> , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
> MatchCase:=False, SearchFormat:=False).Activate
> Selection.End(xlToRight).Select
> ActiveCell.Offset(0, 1).Select
> Range(Selection, Selection.End(xlDown)).Select
> Selection.Cut
> ActiveCell.Offset(0, 1).Select
> ActiveSheet.Paste
> ActiveCell.Offset(0, 1).Select
> ActiveCell.FormulaR1C1 = "=RC[-1]*RC[6]/12"
>
>
> --
> A Waller

Thank you so much! That worked like a charm!

"Bernie Deitrick" wrote:

> A,
>
> Replace
>
> ActiveCell.FormulaR1C1 = "=RC[-1]*RC[6]/12"
>
> with
>
> Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)) _
> .FormulaR1C1 = "=RC[-1]*RC[6]/12"
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "A Waller" <AWaller@discussions.microsoft.com> wrote in message
> news:72C95C96-3CA1-4CBA-B470-4581FCD4B42A@microsoft.com...
> > I am writing a macro. It's more complicated than I originally thought.
> What I
> > need to do is autofill a formula in a column to the last row with data.
> The
> > problem is the rows are dynamic. They start in a different place each time
> > and end in a different place. The range size will vary every time the
> macro
> > is used. Once the formula has calculated the neccessary numbers I want to
> > copy and paste the information back to another column. My problem is
> telling
> > excel which function I want to perform when the calculations will be made
> on
> > a moving target. I have the macro working to the first cell where the
> formula
> > is entered. Now I need to copy the formula to the last row that contains
> > information the formula can use. Please help. I have posted this
> previously
> > but it was days ago and still no answer. Here's a sample of my macro. This
> is
> > only a small part.
> >
> > Sheets("BOMSized").Select
> > Cells.Find(What:="RIM", After:=ActiveCell, LookIn:=xlFormulas _
> > , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> _
> > MatchCase:=False, SearchFormat:=False).Activate
> > Selection.End(xlToRight).Select
> > ActiveCell.Offset(0, 1).Select
> > Range(Selection, Selection.End(xlDown)).Select
> > Selection.Cut
> > ActiveCell.Offset(0, 1).Select
> > ActiveSheet.Paste
> > ActiveCell.Offset(0, 1).Select
> > ActiveCell.FormulaR1C1 = "=RC[-1]*RC[6]/12"
> >
> >
> > --
> > A Waller
>
>
>

You're welcome. Charmed code is a speciality of this newsgroup ;-).

HTH,
Bernie
MS Excel MVP

> Thank you so much! That worked like a charm!