Dominic

You would be better of Nesting a Select Case Statement. See below:

Sub NoLoops()

Select Case Range("A1")

Case 1 To 5

Select Case Range("A2")

Case 10 To 20

Select Case Range("A3")

Case 21 To 30

Select Case Range("A4")

Case 31 To 40

MsgBox "It is"

End Select

End Select

End Select

End Select

End Sub

Dave

OzGrid Business Applications

Dominic

If interested in a formula-based solution, have a look at

Posted by Aladin Akyurek on March 28, 2001 at 15:38:26:

>In Reply To: IF function nesting limitations posted by Valerie on March 28, 2001 at 14:34:29:

>I converted a spreadshhet from Lotus, which had 12 IF arguments and it seems Excel has a limit of 7. Any suggestions? Here is my formula

> =IF(AND(O4>24999,BW4=1),"A1",IF(AND(O4>24999,BW4=0),"A0",IF(AND(AND(O4<25000,O4>19999),BW4=1),"B1",IF(AND(AND(O4<25000,O4>19999),BW4=0),"B0",IF(AND(AND(O4<20000,O4>14999),BW4=1),"C1",IF(AND(AND(O4<20000,O4>14999),BW4=0),"C0",IF(AND(AND(O4<15000,O4>9999),BW4=1),"D1",IF(AND(AND(O4<15000,O4>9999),BW4=0),"D0",IF(AND(O4<10000,BW4=1),"E1",IF(AND(O4<10000,BW4=0),"E0",0))))))))))

Hi Valerie

I believe you can replace this giant-IF with something much simpler. If I understand it correctly (a hard job with this much ifs), you can construct a lookup table. I propose to place the following in one column

0

10000

15000

20000

25000

& the following in a column next to the previous

E0

D0

C0

B0

A0

& what follows in a column next to the previous

E1

D1

C1

B1

A1

Select these 3-column of values and name it, e.g., VALUES.

Then use the following VLOOKUP-formula instead:

=VLOOKUP(O4,VALUES,IF(BW4,3,2),1)

BTW, what are these A0, A1,etc.? Cell addresses? If so, replace them as required by Excel.

Aladin

You can also post a snippet of your data and the relevant conditions that you want to use.

Aladin

See Chip Pearsons" Page for another approach

http://www.cpearson.com/excel/nested.htm

Lenze

Expanding the number of nested IFs, as has been described on Pearson's page, is, in my humble opnion, the most harmful proposal in the world of spreadsheets. It's darn hard for human cognition to process 3 nested IFs. What is the point of setting up of say 14 nested IFs, if you can't hardly debug it when needed. That's the reason why I never brought up that "solution" on this site.

Cheers.

Aladin