Macro to extend formulas
 

I am trying to create a macro to extend formulas in columns downward to the
last filled adjacent cells. i.e. Col A thru C are filled down to row 120. Col
D thru F Row 1 contains the formulas to be extended down to row 120. The
problem is that when I record a macro it refers to specific cells. The last
row filled in Cols A-C is dynamic but when I run the macro it extends the
formula down to the same place each time. I have read some posts that refer
to a "Relative Reference" option in the Stop Recording toolbar but I don't
have this. I am using Access 2003. Any suggestions are greatly appreciated!
Thanks,
Joe M.

Macro to extend formulas
 

Access 2003? Where is your code being written?
--
HTH...

Jim Thomlinson


"Joe M." wrote:

I am trying to create a macro to extend formulas in columns downward to the
last filled adjacent cells. i.e. Col A thru C are filled down to row 120. Col
D thru F Row 1 contains the formulas to be extended down to row 120. The
problem is that when I record a macro it refers to specific cells. The last
row filled in Cols A-C is dynamic but when I run the macro it extends the
formula down to the same place each time. I have read some posts that refer
to a "Relative Reference" option in the Stop Recording toolbar but I don't
have this. I am using Access 2003. Any suggestions are greatly appreciated!
Thanks,
Joe M.

Macro to extend formulas
 

Sorry, I meant to say Excel 2003. I work in both...it's been a long day.

"Jim Thomlinson" wrote:

Access 2003? Where is your code being written?
--
HTH...

Jim Thomlinson


"Joe M." wrote:

I am trying to create a macro to extend formulas in columns downward to the
last filled adjacent cells. i.e. Col A thru C are filled down to row 120. Col
D thru F Row 1 contains the formulas to be extended down to row 120. The
problem is that when I record a macro it refers to specific cells. The last
row filled in Cols A-C is dynamic but when I run the macro it extends the
formula down to the same place each time. I have read some posts that refer
to a "Relative Reference" option in the Stop Recording toolbar but I don't
have this. I am using Access 2003. Any suggestions are greatly appreciated!
Thanks,
Joe M.

Macro to extend formulas
 

Sub Auto_Fill()
Dim lRow As Long
With ActiveSheet
lRow = .Range("C" & Rows.Count).End(xlUp).Row
.Range("D1:F" & lRow).FillDown
End With
End Sub

Assumes you have pre-entered the formulas in D1:F1


Gord Dibben MS Excel MVP

On Wed, 16 Dec 2009 12:43:01 -0800, Joe M.
wrote:

I am trying to create a macro to extend formulas in columns downward to the
last filled adjacent cells. i.e. Col A thru C are filled down to row 120. Col
D thru F Row 1 contains the formulas to be extended down to row 120. The
problem is that when I record a macro it refers to specific cells. The last
row filled in Cols A-C is dynamic but when I run the macro it extends the
formula down to the same place each time. I have read some posts that refer
to a "Relative Reference" option in the Stop Recording toolbar but I don't
have this. I am using Access 2003. Any suggestions are greatly appreciated!
Thanks,
Joe M.

Macro to extend formulas
 

Assuming your current Column D formulas starts in Row 2 and is of this
structure...

=<formula

why not change it to this...

=IF(A2="","",<formula)

and the copy that down to a rows that is located as far down as you ever
think you might ever use, then do the same thing in Columns E and F for
their respective formulas. That way, you won't need to keep executing a
macro every time new data is added (the formulas will only display values
when there is an entry in Column A for that row).

--
Rick (MVP - Excel)


"Joe M." wrote in message
...
I am trying to create a macro to extend formulas in columns downward to the
last filled adjacent cells. i.e. Col A thru C are filled down to row 120.
Col
D thru F Row 1 contains the formulas to be extended down to row 120. The
problem is that when I record a macro it refers to specific cells. The
last
row filled in Cols A-C is dynamic but when I run the macro it extends the
formula down to the same place each time. I have read some posts that
refer
to a "Relative Reference" option in the Stop Recording toolbar but I don't
have this. I am using Access 2003. Any suggestions are greatly
appreciated!
Thanks,
Joe M.