an if loop

[abberyfarm]

Hi there,

I have a sum below and I need to create an if loop. How can I say, if the value calculated is less than zero and rc[-21] is greater than 2000, then return zero. Else return the sum (which will be positive).

Thank you

Range("z2:z" & Lastrow3).FormulaR1C1 = "=rc[-1] + rc[-2] + rc[-3] + rc[-11]"

 

[Peter_SSs]

Hi there,

I have a sum below and I need to create an if loop. How can I say, if the value calculated is less than zero and rc[-21] is greater than 2000, then return zero. Else return the sum (which will be positive).

Thank you

Range("z2:z" & Lastrow3).FormulaR1C1 = "=rc[-1] + rc[-2] + rc[-3] + rc[-11]"

I'm a little confused.

1. I don't see what a loop has to do with it.

2. Why are we checking if column E > 2000 since you appear to be saying that W+X+Y will be positive if W+X+Y < 0 and E > 2000 is not true?

That is, is it possible that W+X+Y < 0 and E <= 2000? If so, what should be returned?

 

[abberyfarm]

I'm a little confused.

1. I don't see what a loop has to do with it.

2. Why are we checking if column E > 2000 since you appear to be saying that W+X+Y will be positive if W+X+Y < 0 and E > 2000 is not true?

That is, is it possible that W+X+Y < 0 and E <= 2000? If so, what should be returned?

Sorry I meant to say an if condition not a loop.

I want it to do, if O+W+X+Y < 0 and E > 2000 then return 0. If O+W+X+Y < 0 and E < 0 then return the value. If O+W+X+Y > 0 and E > 0 the return the value.

Hope that makes sense

Thanks

 

[Peter_SSs]

What about O+W+X+Y < 0 and E between 0 and 2000?

What about O+W+X+Y > 0 and E <= 0?

 

[abberyfarm]

What about O+W+X+Y < 0 and E between 0 and 2000?

What about O+W+X+Y > 0 and E <= 0?

If O+W+X+Y < 0 and E between 0 and 2000 then return the summed value.

If O+W+X+Y > 0 and E <= 0, this would never happen in my case, but to satisfy the condition it should return the summed value also.

Thank you

 

[Peter_SSs]

OK, try this as your FormulaR1C1

"=(RC[-11]+RC[-1]+RC[-2]+RC[-3])*OR(RC[-1]+RC[-2]+RC[-3]+RC[-11]>0,RC[-21]<=2000)"

 

[abberyfarm]

Hi Peter,

That works fine. Thank you for your help.