I am having difficulty debuging some code. I am wandering if I am simply
trying to nest to many If statements.

The code is as follows:

ActiveCell.FormulaR1C1 = _
"=IF(LEFT(RC[1],9)=""NASCO/REP"",""BLANK"",IF(LEFT(RC[1],5)=""HERTZ"",""HERTZ"",IF(LEFT(RC[1],5)=""DALLAS"",""DALLAS"",IF(LEFT(RC[1],5)=""ALFAX"",""ALFAX"",IF(LEFT(RC[1],5)=""OF/US"",""OFUSA"",IF(LEFT(RC[1],5)=""QUILL"",""QUILL"",IF(LEFT(RC[1],5)=""NASCO"",""NASCO"",IF(LEFT(RC[1],5)=""DOANE"",""TERA"",IF(LEFT(RC[1],3)=""NBF"",""NBF"",IF(LEFT(RC[1],3)=""IS-"",""IS"",""K"")))))))))))"

Do I need to find a way to do this w/o clumsy If's?

Josh

josh.eades atgmaildotcom

To answer your question:
1. Yes, you are trying to nest too many If statements. Max. is 7.
2. In spite of that, you have one too many right parentheses for the number
of nested Ifs.
3. Also, there are six characters in "Dallas" so:
IF(LEFT(RC[1],5)=""DALLAS"",""DALLAS"", ...
won't work anyway.

I removed the last 3 nested Ifs and also removed one extra right parentheses
and got it to work.

Suggested change follows. Note use of the OR function for all 5 letter types
that do not change if the cell to the right is the same. This is all one
formula. Wordwrap makes it look like multiple formulas:-

=IF(LEFT(RC[1], 9) = "NASCO/REP", "BLANK", IF(LEFT(RC[1], 5) = "DOANE",
"TERA", IF(LEFT(RC[1], 5) = "OF/US", "OFUSA", IF(LEFT(RC[1], 6) = "DALLAS",
"DALLAS", IF(OR(LEFT(RC[1], 5) = "HERTZ", LEFT(RC[1], 5) = "ALFAX",
LEFT(RC[1], 5) = "QUILL", LEFT(RC[1], 5) = "NASCO"), LEFT(RC[1], 5), "K")))))

Check out VLookup and Match for an alternative strategy.

Regards,
Greg







"Josh" wrote:

I am having difficulty debuging some code. I am wandering if I am simply
trying to nest to many If statements.

The code is as follows:

ActiveCell.FormulaR1C1 = _
"=IF(LEFT(RC[1],9)=""NASCO/REP"",""BLANK"",IF(LEFT(RC[1],5)=""HERTZ"",""HERTZ"",IF(LEFT(RC[1],5)=""DALLAS"",""DALLAS"",IF(LEFT(RC[1],5)=""ALFAX"",""ALFAX"",IF(LEFT(RC[1],5)=""OF/US"",""OFUSA"",IF(LEFT(RC[1],5)=""QUILL"",""QUILL"",IF(LEFT(RC[1],5)=""NASCO"",""NASCO"",IF(LEFT(RC[1],5)=""DOANE"",""TERA"",IF(LEFT(RC[1],3)=""NBF"",""NBF"",IF(LEFT(RC[1],3)=""IS-"",""IS"",""K"")))))))))))"

Do I need to find a way to do this w/o clumsy If's?

Josh

josh.eades atgmaildotcom

There is a limit on nesting functions in general, not just IF. In
Excel 2000 it was 7, and for some reason I'm thinking it is 9 in 2003.

Steven


Je Tue, 11 Apr 2006 15:30:01 -0700, Josh
skribis:

I am having difficulty debuging some code. I am wandering if I am simply
trying to nest to many If statements.

The code is as follows:

ActiveCell.FormulaR1C1 = _
"=IF(LEFT(RC[1],9)=""NASCO/REP"",""BLANK"",IF(LEFT(RC[1],5)=""HERTZ"",""HERTZ"",IF(LEFT(RC[1],5)=""DALLAS"",""DALLAS"",IF(LEFT(RC[1],5)=""ALFAX"",""ALFAX"",IF(LEFT(RC[1],5)=""OF/US"",""OFUSA"",IF(LEFT(RC[1],5)=""QUILL"",""QUILL"",IF(LEFT(RC[1],5)=""NASCO"",""NASCO"",IF(LEFT(RC[1],5)=""DOANE"",""TERA"",IF(LEFT(RC[1],3)=""NBF"",""NBF"",IF(LEFT(RC[1],3)=""IS-"",""IS"",""K"")))))))))))"

Do I need to find a way to do this w/o clumsy If's?

Josh

josh.eades atgmaildotcom


--
Steven M - lid
(remove wax and invalid to reply)

A fool and his money are soon elected. -- Will Rogers