problem inserting formula with R1C1

[antrop]

Dear All,
I have a small problem trying to inserting automatically a formula with VBA in a cell. In practice I need to add an element to an enumerated list and in the formula there should be the variable that indicates the number of this element. To be more clear I post the code:



Sub addel()
Dim nEL As Integer, inPos As Integer, eID As Integer
Dim strFor As String

nEL = 0

Do While Range("eList").Offset(nEL, 0) <> ""
nEL = nEL + 1
Loop

Range("eList").Offset(nEL, 0) = "Element " & nEL
strFor = "= INDEX('Element" & nEL & "'!$C:$C,MATCH(""Name"",'Element " & nEL & "3'!$A:$A,FALSE))"
Range("eList").Offset(nEL, 1).FormulaR1C1 = strFor

End Sub




Unfortunately I get always an application-defined or object-defined error. Some of you maybe with external eyes can see properly my mistakes and this would be great.
Thank you in advance,
Luca.

[Bob Phillips]

You should be using the Formula property, not the FormulaR1C1 property, t he formula string is using A1 notation.

[antrop]

I tried to use the formula property but it gives me the same mistake :banghead:

[Bob Phillips]

It works for me, although I notice that the formula refers to two different sheets.