Macro to extend formulas

[Joe M.]

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.

 

[Jim Thomlinson]

Access 2003? Where is your code being written?

 

[Joe M.]

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

 

[Gord Dibben]

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

 

[Rick Rothstein]

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).