Putting Formula in a Range of Cell

yeheya

Board Regular
Joined
May 20, 2004
Messages
162
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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
How about?:

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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top