Putting Formula in a Range of Cell

yeheya

Board Regular
Hi All,

I have a requirement to paste a formula (any, say B1+C1) into Cell A1:A25515 thru VBA. I can do that one row at a time. Is there a way in VBA to add this formula to A1:A25515 in one shot without looping these many rows?

Any help will be greatly appreciated.

Regards,
Yeheya

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Code:
``````Sub test1()
Range("A1:A25515").FormulaR1C1 = "=RC[1]+RC[2]"
End Sub``````

Note that this doesn't look to see if values exist in Columns B and C. HTH

It's just as easy to do it without using VBA.

Select the cell you want to copy. Now go to cell A1 and in the top left box of the window where you see which cell you are in, type :A25515 after A1.

Now press CTRL + V and the formula will appear in the selected cells.

You could try recording a macro while doing this if you wanted it to do the operation automatically.

Hope this helps.

Thanks..It worked for me.

A small question here:
What is the difference btw two
1.Range("A1:A25515").FormulaR1C1 = "=RC[1]+RC[2]"
2.Range("A1:A25515").Formula = "=RC[1]+RC[2]"

Regards,
Yeheya

As far as I know (hopefully, one of the VBA formula experts can correct me if I misstate this):

When you use .FormulaR1C1, VBA expects to see R1C1 references. If there are references to other cells in the formula (e.g. "=D5+5"), VBA will interpret the D5 as a string literal. So, it will think D5 is a range, or the name of a sheet that doesn't exist.

I try to avoid this by using .FormulaR1C1 when the formula clearly has R1C1 references, .FormulaArray when it's an array formula, or just .Formula when it's neither of those.

A little clearer? Hope so; if not, post back with any specific questions you might have. HTH

Replies
6
Views
125
Replies
9
Views
190
Replies
6
Views
396
Replies
3
Views
99
Replies
14
Views
563

1,216,129
Messages
6,129,055
Members
449,484
Latest member
khairianr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back