Insert Relative Formula Into Range Via VBA

[Chaz1]

Hi All
I am updating a wsheet via a form using vb to locate next empy row etc.
The issue I have is with the formula's no matter how I try I cannot get the formula to increment with each row. I know I can drag and auto fill on the sheet but I want to use vb if poss. eg
Previous entry to sheet:-
A1=1
B1=2
c1=A1+B1(formula)

New entry via form selecting next empty.
A2=(updated from form.)
B2=(Updated from form.)
C2=(Update code VB)

This is all okay and functional the problem starts from here
I so far have been unable to increment the formula in c1 from A1+B1 to A2+B2 using vb, is this possible or am I just chasing my tail.

Thanks for any input
Chaz

[RichardSchollar]

Hi Chaz

Use FormulaR1C1 property rather than Formula so the code you want then becomes:

Range("C2").FormulaR1C1 = "=RC[-1]+RC[-2]" 

This formula applied in any cell will always add the two cells to the immediate left of wherever you enter it.

Richard

[shg]

You want to use formulas to do this?

Cells(1, "A") = 1 
 
Cells(2, "A").Resize(100).FormulaR1C1 = "=r[-1]c + 1" 
 ' or
Cells(2, "A").Resize(100).FormulaR1C1 = "=row() - row(r1c) + 1" 

[Chaz1]

Thanks I just needed to know if it could be done. it may prove useful later in my project.