# Thread: nested ' IF ' more than 7x (officexp 2002)

1. ## nested ' IF ' more than 7x (officexp 2002)

excel only allows the nesting upto 7 deep any one any ideas on how to increase the nesting say upto 20 x ?? all i have been able to find up to now is that it is possible in vba but no examples

2. ## Re: nested ' IF ' more than 7x (officexp 2002)

There are some tricks to getting around the nested 7 ifs:
Use a user-defined function in VB
Use boolean logic
Use some sort of lookup table with the options
Use a concatenation of possible ifs

What is best with an example, would be easier if you stated what you want the formula to do. possibly with example data and then you will most likely get multiple ways to accomplish it.

Steve

3. ## Re: nested ' IF ' more than 7x (officexp 2002)

the following code is used repatatively to place "yes" in a column in preperation for deleting the line . i would like to be able to increase the function so that i can make the macro run quicker.
----------------------------------------------------------------------------------------------------------------------------------------------------

wsh.Range("P4").FormulaR1C1 = _
"=IF(RC[-15]=33070,""yes"",IF(RC[-15]=33080,""yes"",IF(RC[-15]=33180,""yes"",IF(RC[-15]=33126,""yes"",IF(RC[-15]=33085,""yes"",IF(RC[-15]=33185,""yes"",IF(RC[-15]=33087,""yes"","""")))))))"
wsh.Range("P4").Copy Destination:=wsh.Range("P5:P6000")
wsh.Range("Q4").FormulaR1C1 = _
"=IF(RC[-16]=33090,""yes"",IF(RC[-16]=33190,""yes"",IF(RC[-16]=33091,""yes"",IF(RC[-16]=33093,""yes"",IF(RC[-16]=33095,""yes"",IF(RC[-16]=33094,""yes"",IF(RC[-16]=33101,""yes"","""")))))))"
wsh.Range("Q4").Copy Destination:=wsh.Range("Q5:Q6000")
wsh.Range("R4").FormulaR1C1 = _
"=IF(RC[-17]=33099,""yes"",IF(RC[-17]=33097,""yes"",IF(RC[-17]=33150,""yes"",IF(RC[-17]=33135,""yes"",IF(RC[-17]=33136,""yes"",IF(RC[-17]=33100,""yes"",IF(RC[-17]=33105,""yes"","""")))))))"
wsh.Range("R4").Copy Destination:=wsh.Range("R5:R6000")
Application.CutCopyMode = False
Application.MaxChange = 0.001
ActiveWorkbook.PrecisionAsDisplayed = False
Calculate
Range("A4").Select
Selection.AutoFilter
Range("A4:R6000").Select
lastrow = ActiveSheet.UsedRange.Rows.Count
For r = lastrow To 1 Step -1
If LCase(Cells(r, 15).Value) = "yes" Then Rows®.Delete
If LCase(Cells(r, 16).Value) = "yes" Then Rows®.Delete
If LCase(Cells(r, 17).Value) = "yes" Then Rows®.Delete
If LCase(Cells(r, 18).Value) = "yes" Then Rows®.Delete
Next r

4. ## Re: nested ' IF ' more than 7x (officexp 2002)

sorry i seem to have duplicated the posting------------------------------------------------------------------------------------------------------------------------

5. ## Re: nested ' IF ' more than 7x (officexp 2002)

Chip Pearson addresses this very issue.

6. ## Re: nested ' IF ' more than 7x (officexp 2002)

This doesn't really answer the question of your original post, but I think it should SOLVE the underlying problem.

Another option (if you have the list of numbers in a named range in the spreadsheet is just to loop thru the range instead of creating the array.

Steve

<pre>Option Explicit
Dim wsh As Worksheet
Dim lastrow As Long
Dim r As Long

Application.ScreenUpdating = False
BadArray = Array(33070, 33080, 33180, 33126, _
33085, 33185, 33087, 33090, 33190, _
33091, 33093, 33095, 33094, 33101, _
33099, 33097, 33150, 33135, 33136, _
33100, 33105)
Set wsh = Worksheets("sheet1")
lastrow = wsh.UsedRange.Rows.Count

For r = lastrow To 4 Step -1
If DeleteMe(wsh.Cells(r, 1).Value) Then _
wsh.Rows®.Delete
Next r
Application.ScreenUpdating = True
End Sub
Function DeleteMe(vValue) As Boolean
Dim x As Integer
DeleteMe = False
For x = 1 To UBound(BadArray)
DeleteMe = True
Exit Function
End If
Next x
End Function</pre>

7. ## Re: nested ' IF ' more than 7x (officexp 2002)

<img src=/S/hello.gif border=0 alt=hello width=25 height=29> Alexanderd

To add to the great advice you have gotten already, in any case, when you find yourself using too many If statements, IMHO, its time to look at the CASE statement structure.

Look at it, and see how it works, you will like it.

If All first 7 Ifs turn True, then A1 will be True, then if the next 7 Ifs turn True, then B1 will be True, and if the next 7 Ifs turn True, then C1 will be True. Now based on A1, B1, and C1 is all are True then your next 7 Ifs can also be added, and you can go on and on.

You could produce a matrix of Trues and Falses, and work off of that, and there are no limits for that, other than the 65,536 cells in a worksheet, but I guess anything past 10 Ifs is confusing <img src=/S/confused.gif border=0 alt=confused width=15 height=20> enough, and again IMHO should not be used.

Hope this helps.

Wassim

8. ## Re: nested ' IF ' more than 7x (officexp 2002)

Hi Alexander,

Do you really need to nest the IFs? For example:
=IF(A1=1,A1,"")&IF(A1=2,A1,"")&IF(A1=3,A1,"")&IF(A 1=4,A1,"")&IF(A1=5,A1,"")&IF(A1=6,A1,"")&IF(A1=7,A 1,"")&IF(A1=8,A1,"")&IF(A1=9,A1,"")&IF(A1=10,A1,"" )&IF(A1=11,A1,"")&IF(A1=12,A1,"")
will evaluate 12 conditions without nesting, as will:
=IF(A1=1,A1,IF(A1=2,A1,IF(A1=3,A1,IF(A1=4,A1,IF(A1 =5,A1,IF(A1=6,A1,""))))))&IF(A1=7,A1,IF(A1=8,A1,IF (A1=9,A1,IF(A1=10,A1,IF(A1=11,A1,IF(A1=12,A1,""))) )))
with nesting. The result is text, which may not be appropriate, but that can be overcome by surrounding the formula with VALUE(). Then you only have the 1024 char limit to worry about.

Cheers

9. ## Re: nested ' IF ' more than 7x (officexp 2002)

i now have more ammunition than i thought on how to tackle my problem areas.
to all those whose have contributed i thank you .

10. ## Re: nested ' IF ' more than 7x (officexp 2002)

i am sorry i have taken so long to reply to you, but other projects have placed this one on the back burner.
if i use the code as a separate public sub all works ok, what i am having difficulties with now is being able to " call DeleteBad " from within my main programme. which unfortunately stops part way through the "Function DeleteMe" at "Ubound" have you any advise on this

11. ## Re: nested ' IF ' more than 7x (officexp 2002)

1) the function and the code need to be in the same module
2) the public (declaration) of "BadArray" must be in the declaration (before the first sub) of that same module.

The public declaration allows the BadArray variable to be available to all routines in the module. It is filled in the program and used in the function.

Steve

12. ## Re: nested ' IF ' more than 7x (officexp 2002)

Hi alexanderd,

Looking again at you problem, I noticed that the lines in your code:
wsh.Range("P4").FormulaR1C1 = _
"=IF(RC[-15]=33070,""yes"",IF(RC[-15]=33080,""yes"",IF(RC[-15]=33180,""yes"",IF(RC[-15]=33126,""yes"",IF(RC[-15]=33085,""yes"",IF(RC[-15]=33185,""yes"",IF(RC[-15]=33087,""yes"","""")))))))"
all test the same cell (A4), as do the subsequent IF test lines. That being the case, you can get by with just one IF test, with no nesting involved, by incorporating an OR statement, thus:
wsh.Range("P4").Formula = _
"=IF(OR(A4=33070,A4=33080,A4=33085,A4=33087,A4=330 90,A4=33091,A4=33093,A4=33094,A4=33095,A4=33097,A4 =33099,A4=33100,A4=33101,A4=33105,A4=33126,A4=3313 5,A4=33136,A4=33150,A4=33180,A4=33185,A4=33190),"" yes"","""")"

This does much the same as array-processing portion of Steve's macro, except that it leaves the underlying formula accessible in the cells concerned.

Cheers

13. ## Re: nested ' IF ' more than 7x (officexp 2002)

Chip Pearson tackled this problem some time ago. You can find out how to get around the 7 if statement limitations by visiting his website and specifically this page...

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

Dennis

(Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

14. ## Re: nested ' IF ' more than 7x (officexp 2002)

this is becoming serious an i am greatfull for all the input i agree that using
wsh.Range("P4").Formula = _
"=IF(OR(A4=33070,A4=33080,A4=33085,A4=33087,A4=330 90,A4=33091,A4=33093,A4=33094,A4=33095,A4=33097,A4 =33099,A4=ect ect dose work and at present i have reached a maximum of 29 items but to a slowing down of the end result.
i am now going to split this in half and see if it makes a difference.
Steve Aprahamian reply of 23 july is much quicker but as yet i have not found a way to call up the sub from in the main programe.
still trying to achieve a better product for all to use.

15. ## Re: nested ' IF ' more than 7x (officexp 2002)

How do you want to call it?
You could add a command button from the forms toolbar onto the sheet and assign the macro to it
You could create a toolbar item and assign the macro to that.

You could call it from tools -macro- macros

Steve

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•