Syntax question

[bradyj7]

Hi there,

Can someone tell me what it wrong with the syntax in this line? I can't see it

Range("AK3:AK" & Lastrow).FormulaR1C1 = "=If(if(rc[-1] =0, 0, (rc[-2]/rc[-1]) < 0, (rc[-2]/rc[-1])*.2)), if(rc[-1] =0, 0, (rc[-2]/rc[-1]))"

Thanks

 

[Andrew Poulsom]

What formula do you want in AK3 in A1 reference style? If you record a macro while entering it you will get the syntax.

 

[hiker95]

bradyj7,


The formula is failing on the .2

Range("AK3:AK" & Lastrow).FormulaR1C1 = "=If(if(rc[-1] =0, 0, (rc[-2]/rc[-1]) < 0, (rc[-2]/rc[-1])*[SIZE="4"][B].2[/B][/SIZE])), if(rc[-1] =0, 0, (rc[-2]/rc[-1]))"

What is the actual formula for cell AK3?

 

[bradyj7]

The original formula for the column AK was

Range("AK3:AK" & Lastrow).FormulaR1C1 = "=rc[-2]/rc[-1]"

But then if the previous column was zero it would cause an error so I changed it this

Range("AK3:AK" & Lastrow).FormulaR1C1 = "=if(rc[-1] =0, 0, rc[-2]/rc[-1])"

But what I want it to do now is, if rc[-1] is zero then return zero. But also if rc[-2] is negative multiply the answer by 0.2. (i.e rc[-2]/rc[-1]*0.2). But if rc[-2] is positive leave it as rc[-2]/rc[-1].

Does that make sense?

 

[RoryA]

Range("AK3:AK" & Lastrow).FormulaR1C1 = "=if(rc[-1] =0, 0, rc[-2]/rc[-1])*IF(rc[-2]<0,0.2,1)"

should work.

 

[bradyj7]

Thank you!