macro recording
 

I am trying to record a macro, with a very long if-sequence. and it just says
"weren't able to record". I tried writing it in the VB-window as well but it
doesn't work:

this is the formula:
=IF(B2="Tobias";IF(N20,97*I2;IF(M2-1;IF(M2<10;1;0);0);IF(N2<0,03*I2;IF(O1=0;IF(M2-1;IF(M2<10;1;0);1);0);IF(O2=0;IF(M2-1;IF(M2<10;1;0);0);0)));IF(O2=0;IF(M2-1;IF(M2<10;1;0);0);0))

I guess it is too long? How do I then write it in the VB-editor?

When I wrote it in the editor it once worked but then it didn't like the B2
etc as cell references. I was using:

Range("Q2").Select
ActiveCell.FormulaR1C1 = "=IF(B2=""Tobias...etc

Thanks for any help on this matter,
Tobias

Hi Tobias

an IF statement whether typed into a cell or entered into a cell via a
formula can have only 7 IFs nested within the True or False sections of the
original IF statement.

as far as i can tell you exceed that limit (also i'm not sure all your
brackets are in the right place).

So basically, you'ld need to re-work your IF statement maybe using AND and
OR or even VLOOKUP instead.

Cheers
JulieD

"Tobias" wrote in message
...
I am trying to record a macro, with a very long if-sequence. and it just
says
"weren't able to record". I tried writing it in the VB-window as well but
it
doesn't work:

this is the formula:
=IF(B2="Tobias";IF(N20,97*I2;IF(M2-1;IF(M2<10;1;0);0);IF(N2<0,03*I2;IF(O1=0;IF(M2-1;IF(M2<10;1;0);1);0);IF(O2=0;IF(M2-1;IF(M2<10;1;0);0);0)));IF(O2=0;IF(M2-1;IF(M2<10;1;0);0);0))

I guess it is too long? How do I then write it in the VB-editor?

When I wrote it in the editor it once worked but then it didn't like the
B2
etc as cell references. I was using:

Range("Q2").Select
ActiveCell.FormulaR1C1 = "=IF(B2=""Tobias...etc

Thanks for any help on this matter,
Tobias

Well, it works, I know of the seven nested rule, but it acctually gives the
right answers...

anyhow i managed to get the code right in the editor so now it works. (I
just had to figure out how the C1R1-thing worked.)

But; what about the rule of seven? Why does it work? Or maybe it doesn't?
Shouldn't Excel just not accept it if it was wrong, or would it still give me
a value? (even if it somehow overlooked the last two ifs or so)?

Best regards,
Tobias

"JulieD" skrev:

Hi Tobias

an IF statement whether typed into a cell or entered into a cell via a
formula can have only 7 IFs nested within the True or False sections of the
original IF statement.

as far as i can tell you exceed that limit (also i'm not sure all your
brackets are in the right place).

So basically, you'ld need to re-work your IF statement maybe using AND and
OR or even VLOOKUP instead.

Cheers
JulieD

"Tobias" wrote in message
...
I am trying to record a macro, with a very long if-sequence. and it just
says
"weren't able to record". I tried writing it in the VB-window as well but
it
doesn't work:

this is the formula:
=IF(B2="Tobias";IF(N20,97*I2;IF(M2-1;IF(M2<10;1;0);0);IF(N2<0,03*I2;IF(O1=0;IF(M2-1;IF(M2<10;1;0);1);0);IF(O2=0;IF(M2-1;IF(M2<10;1;0);0);0)));IF(O2=0;IF(M2-1;IF(M2<10;1;0);0);0))

I guess it is too long? How do I then write it in the VB-editor?

When I wrote it in the editor it once worked but then it didn't like the
B2
etc as cell references. I was using:

Range("Q2").Select
ActiveCell.FormulaR1C1 = "=IF(B2=""Tobias...etc

Thanks for any help on this matter,
Tobias