Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto extend formulas | Excel Worksheet Functions | |||
I am not able to extend formulas to successive rows | Excel Worksheet Functions | |||
Extend Formulas | Excel Worksheet Functions | |||
Extend formulas | Excel Discussion (Misc queries) | |||
Extend formats and formulas | Excel Discussion (Misc queries) |