Jay Petrulis

[Zac]

Jay,
Can I use coding like the following (an "IF Stmnt")?


.Range("M2:M" & lastrow) = "=IF(RC[-2]<0,RC[]*-1,RC[])"

Where RC[] is the current cell in Col "M".
Or is the current cell labeled as RC?
I get a failure notice (Debug) when I use the "IF".

Thanks,
Zac

[Jay Petrulis]

On 2002-05-06 06:10, Zac wrote:
Jay,
Can I use coding like the following (an "IF Stmnt")?


.Range("M2:M" & lastrow) = "=IF(RC[-2]<0,RC[]*-1,RC[])"

Where RC[] is the current cell in Col "M".
Or is the current cell labeled as RC?
I get a failure notice (Debug) when I use the "IF".

Thanks,
Zac

Hi,

Not quite sure what you are trying to do. This syntax won't work, and the other syntax you tried probably won't work either unless you enable iterations
Tools>Options>check Iteration

You are creating a circular reference here. I try to avoid these if I can, and do any iterative techniques through Goal Seek, Solver or VBA.

NateO posted a workaround for you, where the data is changed to suit your requirements. If you require a worksheet formula, try to adjust a dependent formula if you do not want intentional circular references.

For instance, if K<0, you want the negative M value, so K*M is positive, right? In your calculated cell, use something like =ABS(K*M) and see if that gives you what you need.

HTH,
Jay