Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Error-Error Message
I keep getting an application defined/object defined error on the following;
any ideas what I'm doing wrong here? Could it have to do with the various range names used in the formula? Am just trying to insert a formula into Cell O13. Sub FormulaInColO() Worksheets("MMS for CP").Range("O13").Select Selection.Formula = "=IF(P13=5,13," & _ "IF(M13<5,INDEX(SNT,MATCH(H13,SNT_COLA,0),MATCH(M1 3,SNT_ROW13,0))," & _ "IF(M13=9,+AU13,IF(M13=10,INDEX(SMB,MATCH(H13,SMB_ COLA,0),MATCH(M13,SMB_ROW13,0))," & _ "IF(M13<=8,INDEX(SNTOS,MATCH(H13,SNTOS_COLA,0),MAT CH(M13,SNTOS_ROW13,0))," & _ "IF(M13<=14,INDEX(IPS,MATCH(H13,IPS_COLA,0),MATCH( M13,IPS_ROW13,0))," & _ "IF(M13<=18,INDEX(IPSO,MATCH(H13,IPSSO_COLA,0),MAT CH(M13,IPSSO_ROW13,0)),14)))))" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Error-Error Message
The formula is short two right parentheses and has one too many nesting
levels. It therefore raises an error because it does not qualify as a formula and your code says: Selection.Formula = ... To demonstrate, if you put a single quote in front of the formula inside the quotes this will convert it to a text string and you should be able to paste it to the cell: "'=IF(P13=5,13, ... However, this won't resolve the problem. It only demonstrates that the formula structure is the source. Adding two parentheses to the end of the formula won't fix it because you need to redesign it so that it has at least one fewer nesting levels. Greg "Paige" wrote: I keep getting an application defined/object defined error on the following; any ideas what I'm doing wrong here? Could it have to do with the various range names used in the formula? Am just trying to insert a formula into Cell O13. Sub FormulaInColO() Worksheets("MMS for CP").Range("O13").Select Selection.Formula = "=IF(P13=5,13," & _ "IF(M13<5,INDEX(SNT,MATCH(H13,SNT_COLA,0),MATCH(M1 3,SNT_ROW13,0))," & _ "IF(M13=9,+AU13,IF(M13=10,INDEX(SMB,MATCH(H13,SMB_ COLA,0),MATCH(M13,SMB_ROW13,0))," & _ "IF(M13<=8,INDEX(SNTOS,MATCH(H13,SNTOS_COLA,0),MAT CH(M13,SNTOS_ROW13,0))," & _ "IF(M13<=14,INDEX(IPS,MATCH(H13,IPS_COLA,0),MATCH( M13,IPS_ROW13,0))," & _ "IF(M13<=18,INDEX(IPSO,MATCH(H13,IPSSO_COLA,0),MAT CH(M13,IPSSO_ROW13,0)),14)))))" End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Error-Error Message
Greg, thanks! I'll have to think of another way to do this then. Appreciate
your help! "Greg Wilson" wrote: The formula is short two right parentheses and has one too many nesting levels. It therefore raises an error because it does not qualify as a formula and your code says: Selection.Formula = ... To demonstrate, if you put a single quote in front of the formula inside the quotes this will convert it to a text string and you should be able to paste it to the cell: "'=IF(P13=5,13, ... However, this won't resolve the problem. It only demonstrates that the formula structure is the source. Adding two parentheses to the end of the formula won't fix it because you need to redesign it so that it has at least one fewer nesting levels. Greg "Paige" wrote: I keep getting an application defined/object defined error on the following; any ideas what I'm doing wrong here? Could it have to do with the various range names used in the formula? Am just trying to insert a formula into Cell O13. Sub FormulaInColO() Worksheets("MMS for CP").Range("O13").Select Selection.Formula = "=IF(P13=5,13," & _ "IF(M13<5,INDEX(SNT,MATCH(H13,SNT_COLA,0),MATCH(M1 3,SNT_ROW13,0))," & _ "IF(M13=9,+AU13,IF(M13=10,INDEX(SMB,MATCH(H13,SMB_ COLA,0),MATCH(M13,SMB_ROW13,0))," & _ "IF(M13<=8,INDEX(SNTOS,MATCH(H13,SNTOS_COLA,0),MAT CH(M13,SNTOS_ROW13,0))," & _ "IF(M13<=14,INDEX(IPS,MATCH(H13,IPS_COLA,0),MATCH( M13,IPS_ROW13,0))," & _ "IF(M13<=18,INDEX(IPSO,MATCH(H13,IPSSO_COLA,0),MAT CH(M13,IPSSO_ROW13,0)),14)))))" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I have error message when formula is over value? | Excel Worksheet Functions | |||
Error message #NAME from formula | Excel Worksheet Functions | |||
error message: compile error, argument not optional | Excel Programming | |||
Formula Error Message | Excel Discussion (Misc queries) | |||
How do I get rid of "Compile error in hidden module" error message | Excel Discussion (Misc queries) |