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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,379
Members
412,589
Latest member
ArtBOM
Top