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!

 

[Mr2017]

No problem.

Thank you.

 

[Rick Rothstein]

You can also fill down using a direct assignment...

[table="width: 500"]
[tr]
	[td]Sub FillFormulasDown()
  Range("E1:F" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = Range("E1:F1").Formula
End Sub[/td]
[/tr]
[/table]

Or, written slightly differently...

[table="width: 500"]
[tr]
	[td]Sub FillFormulasDown()
  Range("E1:F1").Resize(Range("A1").CurrentRegion.Rows.Count).Formula = Range("E1:F1").Formula
End Sub[/td]
[/tr]
[/table]

 

[Mr2017]

Ok, thanks for the additional options, Rick.

 

[Mr2017]

Hi Fluff

Do you know how to amend the code so that it copies the formulae from the cells specified in row 1 ie cells E1 and F1 BUT it then pastes the formula into cells E5 and F5 and drags them down to the bottom of the range?

I've pasted new data below, for your convenience.

The data in cells A1 to D1 is 4, 6, 3 and 2, respectively. Then cells A5 to B8 have the value 1 in them and C5 to D8 have the value 2.

Then the formulas in cells E1 and F1 are =A1+B1, and =C1+D1.

Please let me know if you need any more info.

Thanks in advance.

4	6	3	2
1	1	2	2
1	1	2	2
1	1	2	2
1	1	2	2

<colgroup><col width="64" span="6" style="width: 48pt;"></colgroup><tbody>
</tbody>

 

[Fluff]

In future please do not duplicate your posts as it just creates unneeded clutter.

Try

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

 

[Mr2017]

In future please do not duplicate your posts as it just creates unneeded clutter.

Try

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

Ok. Apologies.

When I ran the code above, however, although it filled in the formulae in the correct range, it didn't give the formulae excpted.

Eg in cell E5, the formula was =A1+B1 instead of A5+B5.

Do you know how I can amend it so that it fills down formulae which are relevant to the row they're on?

Also, if you could clarify why you used offset (,5) in the code, I'd be grateful - presumably because you wanted to paste the formula in the column 5 columns away from column A?

Thanks in advance.

 

[Fluff]

Try

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

presumably because you wanted to paste the formula in the column 5 columns away from column A?

That's exactly right, where col B is 1 column away, col C is 2 cols etc

 

[Mr2017]

Ok, thanks for clarifying that. I appreciate it!

This code works, but the formula changes in increments of 2 - so

the formula in cell E5 is =A5+B5

the formula in cell E6 is =A7+B7

the formula in cell E7 is =A9+B9

Do you know how I can amend it so it drags in increments of 1 instead of 2, please?

 

[Fluff]

Try

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

 

[Mr2017]

Ok, that works.

Thanks.