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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
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
 

j844929

Active Member
Joined
Aug 18, 2002
Messages
423
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.
 

yeheya

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

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,834
Messages
5,855,899
Members
431,772
Latest member
dannyboi1

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
Top