Formula in VBA

[Chissa]

Hi there,

Can I type normal excel formulas in to VBA? I have the below formula but I need to incorporate it in to my VBA code:

IF(AND(D3>0,D3<999),0&D3,IF(D3>999,D3,IF(D3=0,"0000")))

I so far have written this (with the help from Robert earlier),

Dim LastRow2 As Long
LastRow2 = Range("E" & Rows.Count).End(xlUp).Row

Range("E3:E" & LastRow2).Formula = "=IF(AND(D3>0,D3<999),0&D3,IF(D3>999,D3,IF(D3=0,"0000")))"

But it brings up an error message saying "Compile Error Syntax Error" and I have no idea why.

Hope you can help me

Chissa

 

[Jon von der Heyden]

You need double quotes if it is housed within quotes:

"=IF(AND(D3>0,D3<999),0&D3,IF(D3>999,D3,IF(D3=0,[B][COLOR="red"]"[/COLOR][/B]"0000"[COLOR="Red"][B]"[/B][/COLOR])))"

 

[Chissa]

Something so simple, yet so difficult for me to notice.

Thank you very much

 

[anandvarma]

Hi there,

Can I type normal excel formulas in to VBA? I have the below formula but I need to incorporate it in to my VBA code:

IF(AND(D3>0,D3<999),0&D3,IF(D3>999,D3,IF(D3=0,"0000")))

I so far have written this (with the help from Robert earlier),

Dim LastRow2 As Long
LastRow2 = Range("E" & Rows.Count).End(xlUp).Row

Range("E3:E" & LastRow2).Formula = "=IF(AND(D3>0,D3<999),0&D3,IF(D3>999,D3,IF(D3=0,"0000")))"

But it brings up an error message saying "Compile Error Syntax Error" and I have no idea why.

Hope you can help me

Chissa

try this

Range("E3:E10" & LastRow2).xlFormula = "=IF(AND(D3>0,D3<999),0&D3,IF(D3>999,D3,IF(D3=0,0,false)))"