View Full Version : problem inserting formula with R1C1

07-23-2012, 11:58 PM
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

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,

07-24-2012, 12:02 AM
You should be using the Formula property, not the FormulaR1C1 property, t he formula string is using A1 notation.

07-24-2012, 12:13 AM
I tried to use the formula property but it gives me the same mistake :banghead:

07-24-2012, 01:10 AM
It works for me, although I notice that the formula refers to two different sheets.