Text to Formula

[StevieBoy]

I am attempting to produce a vlookup statement in a cell using VBA but I can
only get it to produce a text version of the formula I require.
The only way I know how to then convert this to an actual working formula is
by editing the cell using the f2 key
Any ideas how I can convert this to a formula (ideally in VBA) witout using
the edit function.

eg. In VBA I can use the following code - the copy and paste special turns
this in to the formula I require but only seems to be appearing as text and
not actually calculating.

ActiveCell.FormulaR1C1 = _
"=""=VLOOKUP($D9,'Y:Buying books\[Fruit - ""&C9&"" Quote
Sheet.xls]Data'!$B$7:$BZ$957,F$1,0)"""
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

I do not want to have to edit the cell in VBA as this puts in a specific
name where I want it to look at cell C9.....

Any ideas???

 

[OssieMac]

The best way to overcome a problem of how to insert a formula using VBA is to
firstly create the formula on the worksheet and get it working to your
satisfaction.

Then turn on the VBA macro recording, select the cell with the formula and
make a pseudo change like delete the last bracket and reinsert it on the
formula bar and then press Enter.

Stop the VBA recorder and open the VBA editor. The code will be in a
standard module. Could be a new module.