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(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

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(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, 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(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