CODEWITHSUNDEEP
vbaexcelexcel-formula
Vpp Man
Vpp Man

Reputation: 2546

Set formula to a range of cells

this is simple demo of what i want to do. I want to set a formula to a range of cells(eg. C1 to C10).

Range("C1").Formula = "=A1+B1"

but how to make formula use dynamic cells like this:

Range("C1:C10").Formula = "=Ax+Bx"

so in reality it is like,

C1 = A1 + B1
C2 = A2 + B2
C3 = A3 + B3
C4 = A4 + B4
C5 = A5 + B5
...
C10 = A10 + B10

how to change RHS of this formula to make above working: Range("C1:C10").Formula = "=Ax+Bx"

Upvotes: 28

Views: 181530

Answers (5)

lineil
lineil

Reputation: 1038

Range("C1:C10").Formula = "=A1+B1"

Simple as that.

It autofills (FillDown) the range with the formula.

Upvotes: 71

Suyog Patil
Suyog Patil

Reputation: 23

Use this 

            Sub calc()


            Range("C1:C10").FormulaR1C1 = "=(R10C1+R10C2)"


            End Sub

Upvotes: 1

Baumann
Baumann

Reputation: 1159

I think this is the simplest answer possible: 2 lines and very comprehensible. It emulates the functionality of dragging a formula written in a cell across a range of cells.

Range("C1").Formula = "=A1+B1"
Range("C1:C10").FillDown

Upvotes: 11

user2626806
user2626806

Reputation: 101

Use FormulaR1C1:

Cells((1,3),(10,3)).FormulaR1C1 = "=RC[-2]+RC[-1]"

Unlike Formula, FormulaR1C1 has relative referencing.

Upvotes: 10

The King
The King

Reputation: 4650

I would update the formula in C1. Then copy the formula from C1 and paste it till C10...

Not sure about a more elegant solution

Range("C1").Formula = "=A1+B1"
Range("C1").Copy
Range("C1:C10").Pastespecial(XlPasteall)

Upvotes: 9

Related Questions