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

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

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" wrote in message
...
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

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

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" wrote in message
...
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

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

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!