Passing on a formula with quotation marks - Help urgently needed!

[Oliver Kharraz]

Hello,

I have a problem programming excel that is driving me nuts.

The following code fails:

Dim s As String
s = "=VLOOKUP(" & Chr(34) & "Timeline" & Chr(34) &
";ScenarioLookUp;2;FALSE)"
Data.Cells(OFormulaValueRow, OFormulaValueCol).Formula = s

whereas when you copy the string s and paste it into a cell manualy, it
gives the correct result.

Just to drive me crazy this snippet works:

s = "=" & Chr(34) & "Test" & Chr(34)
Data.Cells(OFormulaValueRow, OFormulaValueCol).Formula = s

so it is not the quotation mark or the cell references that do not work.

Any help is greatly, very greatly appreciated,

Oliver

 

[Chip Pearson]

Oliver,

VBA is very American-centric, so you probably need to use commas
rather than semicolons as separators in the formula. Or,
perhaps, assign the string to the FormulaLocal property.

s = "=VLOOKUP(" & Chr(34) & "Timeline" & Chr(34) &
",ScenarioLookUp,2,FALSE)"



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com