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 |
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 |
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 |
All times are GMT +1. The time now is 12:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com