How to use a text in a cell as a formula in VBA.

[Martijn]

I would like to make a flexible macro that uses the formula in row 3 of a column to calculate the results of row 5 to last (32333 f.e.)

This is my code so far:
Sub formuletest()
Rijen = Range("AS1").Value
kolom = Range("AS2").Value
formule = Range("AS3").Formula
For i = 5 To Rijen
T_lu = Range("M" & i).Value
Worksheets(1).Cells(i, kolom).Value = formule
Next i
End Sub

formula in AS3 is =polynome(T_lu) and it is already made in VBA.
in my code in row 7 I get:
Worksheets(1).Cells(i, kolom).Value = "=polynome(T_lu)" whitch doesn't do the trick...
It would be nice if this could be any formula that I write in AS3

Thanx
Martijn

 

[xenou]

Hi, usually use copy with a formula:

Range("A1").Copy  Destination:=Range("B1")

Or with many cells:

Range("A1").Copy Destination:=Range("B1:B1000")

That will copy the formula from A1 to the destination cell, with the same mixed or relative referencing as in the original formula.

seems like you need to change the value in parentheses in the formula though. So maybe:

Dim MyFormule

MyFormule = "=polynome(" & T_lu & ")"
Worksheets(1).Cells(i, kolom)[COLOR="Blue"].Formula[/COLOR] = MyFormule

Or, if Cell AS3 contains not the whole formula but just its name:

MyFormule = "=" & Range("AS3").Value & "(" & T_lu & ")"
Worksheets(1).Cells(i, kolom)[COLOR="Blue"].Formula[/COLOR] = MyFormule

--you can see this is getting trickier as we go. But you want to build the String which is the formula you need, then enter it as the .Formula Property or .FormulaR1C1 property, not the .Value property.

Alex