Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Recording a macro ... | Excel Discussion (Misc queries) | |||
automatic macro update | Excel Worksheet Functions | |||
Date macro | Excel Discussion (Misc queries) | |||
Can't get simple macro to run | Excel Worksheet Functions | |||
Macro and If Statement | Excel Discussion (Misc queries) |