Problem writing formulas - please, please help!

[Oliver Kharraz]

Hello,

I have a problem 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 snippes 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

 

[Jan Karel Pieterse]

Hi Oliver,

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.

Because VBA speaks American, it expects you to use the comma as the
argument separator.

I suspect this will work:

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

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com