nested ‘ IF ‘ more than 7x (officexp 2002)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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=33090,A4=33091,A4=33093,A4=33094,A4=33095,A4=33097,A4=33099,A4=33100,A4=33101,A4=33105,A4=33126,A4=33135,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
[Fmr MS MVP - Word]

Reply | Quote

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=33090,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.

Reply | Quote

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

Reply | Quote

good morning , thank you for your response.
i would like to be able to call the ” DeleBad ” from inside the main programe

Reply | Quote

You didn’t really answer the question of HOW?

I will make a guess, if not what you are after, please post back:
With the worksheet viewable:
open the forms toolbar (View – toolbars – “check” Forms)
The item we are interested in is the “Command Button”
If Forms is docked this is the 4th item, if not docked it is the 2nd one down in the right column. [If you move your cursor over the items a “tool tip” will appear and it will say “Command Button”]
Click on the icon and then go to where on the worksheet you want to place it.
Press the left mouse button down when you are in the spot you want the upper left corner of the button to be, and while holding the left mouse button, drag the rectangular shape for the size of the button you want.
Release the left-mouse button and the “Assign macro” dialog will appear.
Select “DeleteBad” from the list
While the “button” is still selected Highlight the text “Button 1” and change the text to something more appropriate to tell the users what it is for. “Press Me to delete the bad rows” for example. If the text does not all fit, you can use the “handles” to change the size and shape.
You can close the forms toolbar if not docked (“X” in upper right) or goto view-tooolbars and uncheck it.

Now when you press the button the macro will run. Be aware there is not undo for macros, so test it on a copy.
Steve

Reply | Quote

what i am trying to do is replace the many lines of:-
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””,””””)))))))”

with your module ” BadArray ” i know it works fine if i stop my program before the above and then run “BadArray” but cannot figure out how or where to introduce it.

Reply | Quote

in a macro routine you can just “call it”
You could replace your current code with the simple line:

BadArray

In the place in the code when you want the macro to run.

Steve

Reply | Quote

in a macro routine you can just “call it”
You could replace your current code with the simple line:

BadArray

In the place in the code when you want the macro to run.

Steve

Reply | Quote

what i am trying to do is replace the many lines of:-
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””,””””)))))))”

with your module ” BadArray ” i know it works fine if i stop my program before the above and then run “BadArray” but cannot figure out how or where to introduce it.

Reply | Quote

You didn’t really answer the question of HOW?

I will make a guess, if not what you are after, please post back:
With the worksheet viewable:
open the forms toolbar (View – toolbars – “check” Forms)
The item we are interested in is the “Command Button”
If Forms is docked this is the 4th item, if not docked it is the 2nd one down in the right column. [If you move your cursor over the items a “tool tip” will appear and it will say “Command Button”]
Click on the icon and then go to where on the worksheet you want to place it.
Press the left mouse button down when you are in the spot you want the upper left corner of the button to be, and while holding the left mouse button, drag the rectangular shape for the size of the button you want.
Release the left-mouse button and the “Assign macro” dialog will appear.
Select “DeleteBad” from the list
While the “button” is still selected Highlight the text “Button 1” and change the text to something more appropriate to tell the users what it is for. “Press Me to delete the bad rows” for example. If the text does not all fit, you can use the “handles” to change the size and shape.
You can close the forms toolbar if not docked (“X” in upper right) or goto view-tooolbars and uncheck it.

Now when you press the button the macro will run. Be aware there is not undo for macros, so test it on a copy.
Steve

Reply | Quote

good morning , thank you for your response.
i would like to be able to call the ” DeleBad ” from inside the main programe

Reply | Quote

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

Reply | Quote

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=33090,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.

Reply | Quote

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=33090,A4=33091,A4=33093,A4=33094,A4=33095,A4=33097,A4=33099,A4=33100,A4=33101,A4=33105,A4=33126,A4=33135,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
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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(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,””)
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
[Fmr MS MVP - Word]

Reply | Quote

Chip Pearson addresses this very issue.

Reply | Quote

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 enough, and again IMHO should not be used.

Hope this helps.

Wassim

Reply | Quote

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%5B/url%5D

Dennis

(Edited by HansV to make URL clickable – see Help 19)

Reply | Quote

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%5B/url%5D

Dennis

(Edited by HansV to make URL clickable – see Help 19)

Reply | Quote