Copying formulas in a Macro

[Guest]

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

 

[Don Guillett]

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

 

[Guest]

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?

 

[Don Guillett]

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
(e-mail address removed)

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?

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

 

[Guest]

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?

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
(e-mail address removed)

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?

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

 

[Tom Ogilvy]

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

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?

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
(e-mail address removed)

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?

:

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