Write cell formula w VBA

[StanSz]

I've written several formulas via VBA without issue, but I'm having a problem with 2 formulas. I thought I had a resolution of the issue using a solution found on an Excel VBA site but that has not resolved the problem:

Here's what I have as the VBA code for testing. I'm getting a 'RunTime 1004 error' in the two last lines:

Sub Testing()

Dim i As Integer
Dim zFormula As String
Dim aeFormula As String

i = 11

zformula = "=IF(F11="",0,-F11*(E11*100))"
aeFormula = "=IF(D11<>"",D11/I11,"")"

Range("Q" & i).Formula = zFormula 'Gives Runtime error 1004

Range("S" & i).Formula = aeFormula 'Gives Runtime error 1004

End Sub

Any thoughts or solutions ??

Thanks

Stansz

 

[Norie]

Stansz

You need to double up the quotes in the formulas, try this.

zformula = "=IF(F11="""",0,-F11*(E11*100))"
aeFormula = "=IF(D11<>"""",D11/I11,"""")"

 

[StanSz]

Stansz

You need to double up the quotes in the formulas, try this.

zformula = "=IF(F11="""",0,-F11*(E11*100))"
aeFormula = "=IF(D11<>"""",D11/I11,"""")"

Thank you -- it worked perfectly.