Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Chip Pearson addresses this very issue.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
    Public BadArray As Variant
    Sub DeleteBad()
    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)
    If BadArray(x) = vValue Then
    DeleteMe = True
    Exit Function
    End If
    Next x
    End Function</pre>


  7. #7
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    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.

    But to answer your question think of teiring these Ifs, say:

    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
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

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

    Paul Edstein
    [MS MVP - Word]

  9. #9
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

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

    Paul Edstein
    [MS MVP - Word]

  13. #13
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    108
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #14
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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 LastLast

Posting Permissions

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