Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying formulas in a Macro
I've written a macro that enters formulas in cells W2 to Z2. I want to fill
these columns with the formulas from the last populated row which is end.down in column G. I've recorded the macro below but this always refers to specific cells rather than cells in Cols W and X relative to the last populated cell in Col G e.g end.down in Col G, then select RC[16]:RC[17], shift end up edit fill Any ideas would be much appreciated. Thanks Application.CutCopyMode = False Range("W2").Select ActiveCell.FormulaR1C1 = "=IF(TYPE(RC7)=2,0,ROUND(RC13*25%,2))" Range("X2").Select ActiveCell.FormulaR1C1 = "=IF(TYPE(RC7)=2,0,ROUND(RC19*25%,2))" Range("G1").Select Selection.End(xlDown).Select Range("W4644:X4644").Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying formulas in a Macro
That works a treat Don, thanks.
As I've got multiple columns I presume I just repeat for each column and formula. Is there a way of putting the formulas in row 2 and copying formulas in multiple columns in one action? "Keith" wrote: I've written a macro that enters formulas in cells W2 to Z2. I want to fill these columns with the formulas from the last populated row which is end.down in column G. I've recorded the macro below but this always refers to specific cells rather than cells in Cols W and X relative to the last populated cell in Col G e.g end.down in Col G, then select RC[16]:RC[17], shift end up edit fill Any ideas would be much appreciated. Thanks Application.CutCopyMode = False Range("W2").Select ActiveCell.FormulaR1C1 = "=IF(TYPE(RC7)=2,0,ROUND(RC13*25%,2))" Range("X2").Select ActiveCell.FormulaR1C1 = "=IF(TYPE(RC7)=2,0,ROUND(RC19*25%,2))" Range("G1").Select Selection.End(xlDown).Select Range("W4644:X4644").Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying formulas in a Macro
For that you need to go back to your original idea of copy down. Just record
and then modify to suit using the idea of finding the last row that is in my macro. Post your code back here for additional comments by us. -- Don Guillett SalesAid Software "nospaminlich" wrote in message ... That works a treat Don, thanks. As I've got multiple columns I presume I just repeat for each column and formula. Is there a way of putting the formulas in row 2 and copying formulas in multiple columns in one action? "Keith" wrote: I've written a macro that enters formulas in cells W2 to Z2. I want to fill these columns with the formulas from the last populated row which is end.down in column G. I've recorded the macro below but this always refers to specific cells rather than cells in Cols W and X relative to the last populated cell in Col G e.g end.down in Col G, then select RC[16]:RC[17], shift end up edit fill Any ideas would be much appreciated. Thanks Application.CutCopyMode = False Range("W2").Select ActiveCell.FormulaR1C1 = "=IF(TYPE(RC7)=2,0,ROUND(RC13*25%,2))" Range("X2").Select ActiveCell.FormulaR1C1 = "=IF(TYPE(RC7)=2,0,ROUND(RC19*25%,2))" Range("G1").Select Selection.End(xlDown).Select Range("W4644:X4644").Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying formulas in a Macro
Thanks a lot
I've tried numerous variations but haven't cracked it. Here is my latest attempt: Sub balance() Application.CutCopyMode = False Range("W2").Select ActiveCell.FormulaR1C1 = "=IF(TYPE(RC7)=2,0,ROUND(RC13*25%,2))" Range("X2").Select ActiveCell.FormulaR1C1 = "=IF(TYPE(RC7)=2,0,ROUND(RC19*25%,2))" Range ("w2:x" & Range("g65536").End(xlUp).Row) Selection.FillDown End Sub Am I close? "Don Guillett" wrote: For that you need to go back to your original idea of copy down. Just record and then modify to suit using the idea of finding the last row that is in my macro. Post your code back here for additional comments by us. -- Don Guillett SalesAid Software "nospaminlich" wrote in message ... That works a treat Don, thanks. As I've got multiple columns I presume I just repeat for each column and formula. Is there a way of putting the formulas in row 2 and copying formulas in multiple columns in one action? "Keith" wrote: I've written a macro that enters formulas in cells W2 to Z2. I want to fill these columns with the formulas from the last populated row which is end.down in column G. I've recorded the macro below but this always refers to specific cells rather than cells in Cols W and X relative to the last populated cell in Col G e.g end.down in Col G, then select RC[16]:RC[17], shift end up edit fill Any ideas would be much appreciated. Thanks Application.CutCopyMode = False Range("W2").Select ActiveCell.FormulaR1C1 = "=IF(TYPE(RC7)=2,0,ROUND(RC13*25%,2))" Range("X2").Select ActiveCell.FormulaR1C1 = "=IF(TYPE(RC7)=2,0,ROUND(RC19*25%,2))" Range("G1").Select Selection.End(xlDown).Select Range("W4644:X4644").Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying formulas in a Macro
Sub balance()
Dim frng As Range Application.CutCopyMode = False Range("W2").FormulaR1C1 = "=IF(TYPE(RC7)=2,0,ROUND(RC13*25%,2))" Range("X2").FormulaR1C1 = "=IF(TYPE(RC7)=2,0,ROUND(RC19*25%,2))" Set frng = Range("w2:x" & Range("g65536").End(xlUp).Row) frng.FillDown End Sub -- Regards, Tom Ogilvy "nospaminlich" wrote in message ... Thanks a lot I've tried numerous variations but haven't cracked it. Here is my latest attempt: Sub balance() Application.CutCopyMode = False Range("W2").Select ActiveCell.FormulaR1C1 = "=IF(TYPE(RC7)=2,0,ROUND(RC13*25%,2))" Range("X2").Select ActiveCell.FormulaR1C1 = "=IF(TYPE(RC7)=2,0,ROUND(RC19*25%,2))" Range ("w2:x" & Range("g65536").End(xlUp).Row) Selection.FillDown End Sub Am I close? "Don Guillett" wrote: For that you need to go back to your original idea of copy down. Just record and then modify to suit using the idea of finding the last row that is in my macro. Post your code back here for additional comments by us. -- Don Guillett SalesAid Software "nospaminlich" wrote in message ... That works a treat Don, thanks. As I've got multiple columns I presume I just repeat for each column and formula. Is there a way of putting the formulas in row 2 and copying formulas in multiple columns in one action? "Keith" wrote: I've written a macro that enters formulas in cells W2 to Z2. I want to fill these columns with the formulas from the last populated row which is end.down in column G. I've recorded the macro below but this always refers to specific cells rather than cells in Cols W and X relative to the last populated cell in Col G e.g end.down in Col G, then select RC[16]:RC[17], shift end up edit fill Any ideas would be much appreciated. Thanks Application.CutCopyMode = False Range("W2").Select ActiveCell.FormulaR1C1 = "=IF(TYPE(RC7)=2,0,ROUND(RC13*25%,2))" Range("X2").Select ActiveCell.FormulaR1C1 = "=IF(TYPE(RC7)=2,0,ROUND(RC19*25%,2))" Range("G1").Select Selection.End(xlDown).Select Range("W4644:X4644").Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying formulas | Excel Worksheet Functions | |||
Copying Sum Formulas | Excel Worksheet Functions | |||
Copying Formulas | Excel Discussion (Misc queries) | |||
copying formulas | Excel Discussion (Misc queries) | |||
Copying macro formulas down | Excel Programming |