How to paste formulas down to the end of a range using VBA

[Mr2017]

Hi Guys!

I'll provide a simple example of what I'd like to do:

I have some formulae in cells E1 and F1 and I'd like to copy and paste them to cell E2 to F2 AFTER data has been pasted in the preceding columns (A:D) until the end of the range of the data in columns A to D.

The data in columns A to D could go from row 2 to row 100, or from row 2 to row 1000.

Does anyone know how to do this, please?

I've pasted very simple formulae below, to save you time:

This formula should go in cell E1: =A1+B1

This formula should go in cell F1: ==C1+D1

Thanks in advance!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Mr2017]

Thanks for the update, Fluff.

But I've got a quick question - with the real data, the code has only worked partially - the formulae are in cells BY1 to CL1.

And the code correctly copied the formula over to cells BY5 to CL5.

BUT it only dragged the formulae down for columns BY and BZ. The subsequent columns (CA to CL) only had the formula added to row 5.

Do you know why this would be? Do I need to amend the R1C1 reference?

I used this:

Sub FillFormulae()
   Range("BY5:CL5").Formula = Range("BY1:CL1").FormulaR1C1
   Range("BY5", Range("A" & Rows.Count).End(xlUp).Offset(, 77)).FillDown
End Sub

Thanks in advance.

 

[Fluff]

You need to change the 77 to 90, ie 90columns to the right of col A

 

[Mr2017]

Ok, thanks.

I thought so.

I’d tried that earlier, but it hadn’t worked - hence the reason for my post above.

But I must have made a typo, as I’ve tried it again, after you suggested it and it’s worked!

Thanks for your help!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Mr2017]

Hi Fluff

Do you know how to paste the formatting as well?

At present, the code is just pasting the formulae, but not the formatting....

 

[Fluff]

Like

Sub Mr2017()
   Range("E1:F1").Copy Range("E5:F5")
   Range("E5", Range("A" & Rows.Count).End(xlUp).Offset(, 5)).Filldown
End Sub

 

[Mr2017]

Ok, thanks. I'll test this out later.