Formula Error-Error Message

[Paige]

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(M13,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),MATCH(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),MATCH(M13,IPSSO_ROW13,0)),14)))))"
End Sub

 

[Greg Wilson]

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]

Greg, thanks! I'll have to think of another way to do this then. Appreciate
your help!