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

Copying formulas in a Macro
 

Use this idea. Modify to suit

Sub balance()
Set frng = Range("w1:w" & Range("g65536").End(xlUp).Row)
With frng
.Formula = "=yourformula"
.Formula = .Value ' to change formulas to values
End With
End Sub

--
Don Guillett
SalesAid Software

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

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

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

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

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