Excel inserts single quote marks into formula

[Max Bialystock]

When I use vba to insert this formula:
Sub Macro1()
ActiveCell.FormulaR1C1 = "=IF(F12=0," & Chr(34) & Chr(34) & Chr(34) &
Chr(34) & ",IF(F12<0.3,0.3-F12," & Chr(34) & Chr(34) & Chr(34) & Chr(34) &
"))"

End Sub
I get this result:
=IF('F12'=0,"""",IF('F12'<0.3,0.3-'F12',""""))

How do I stop Excel from inserting the single quotation marks?

Cheers,
Max

[Bob Phillips]

You cannot use FormulaR1C1 with an A1 style formula. Try

ActiveCell.Formula = "=IF(F12=0," & Chr(34) & Chr(34) & Chr(34) & _
Chr(34) & ",IF(F12<0.3,0.3-F12," & Chr(34) & Chr(34) & Chr(34) & _
Chr(34) & "))"

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Max Bialystock" <[email protected]> wrote in message
news:[email protected]...
> When I use vba to insert this formula:
> Sub Macro1()
> ActiveCell.FormulaR1C1 = "=IF(F12=0," & Chr(34) & Chr(34) & Chr(34) &
> Chr(34) & ",IF(F12<0.3,0.3-F12," & Chr(34) & Chr(34) & Chr(34) & Chr(34) &
> "))"
>
> End Sub
> I get this result:
> =IF('F12'=0,"""",IF('F12'<0.3,0.3-'F12',""""))
>
> How do I stop Excel from inserting the single quotation marks?
>
> Cheers,
> Max
>
>

[Ardus Petus]

FormulaR1C1 expects R1C1 references.

Enter Activecell.Formula = "=IF(F12=0,"""",IF(F12<0.3,0.3-F12,"""")"

HTH
--
AP

"Max Bialystock" <[email protected]> a écrit dans le message de news:
[email protected]...
> When I use vba to insert this formula:
> Sub Macro1()
> ActiveCell.FormulaR1C1 = "=IF(F12=0," & Chr(34) & Chr(34) & Chr(34) &
> Chr(34) & ",IF(F12<0.3,0.3-F12," & Chr(34) & Chr(34) & Chr(34) & Chr(34) &
> "))"
>
> End Sub
> I get this result:
> =IF('F12'=0,"""",IF('F12'<0.3,0.3-'F12',""""))
>
> How do I stop Excel from inserting the single quotation marks?
>
> Cheers,
> Max
>
>