antrop
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
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.
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.