Using an If statement in a macro with multiple condtions. How do you do it?

[drew.j.harrison]

I am trying to get my list to sort based on the combined values of three check boxes. I created a line for each possible combination and for some reason it doesn't work properly. I think it has to do with my coding to get it to only perform an action if all three values are met. Here is the code, let me know if you see a problem:

If Range("AH2") = True And Range("AH3") = True And Range("AH4") = True Then
Selection.AutoFilter Field:=25 'Note : All
ElseIf Range("AH2") = False And Range("AH3") = True And Range("AH4") = True Then
Selection.AutoFilter Field:=25, Criteria1:="<>High" 'Note : Medium and Low
ElseIf Range("AH2") = False And Range("AH3") = False And Range("AH4") = True Then
Selection.AutoFilter Field:=25, Criteria1:="<>High", Operator:=xlAnd, _
Criteria2:="<>Medium" 'Note : Low only
ElseIf Range("AH2") = False And Range("AH3") = False And Range("AH4") = False Then
Selection.AutoFilter Field:=25, Criteria1:="=" 'Note: None Selected
ElseIf Range("AH2") = True And Range("AH3") = False And Range("AH4") = True Then
Selection.AutoFilter Field:=25, Criteria1:="<>Medium" 'Note : High and Low
ElseIf Range("AH2") = True And Range("AH3") = False And Range("AH4") = False Then
Selection.AutoFilter Field:=25, Criteria1:="<>Medium", Operator:=xlAnd, _
Criteria2:="<>Low" 'Note : High only
ElseIf Range("AH2") = Trues And Range("AH3") = True And Range("AH4") = False Then
Selection.AutoFilter Field:=25, Criteria1:="<>Low" 'Note : High and Medium
ElseIf Range("AH2") = False And Range("AH3") = True And Range("AH4") = False Then
Selection.AutoFilter Field:=25, Criteria1:="<>High", Operator:=xlAnd, _
Criteria2:="<>Low" 'Note : Medium only

 

[JackDanIce]

IMO I think a SELECT CASE statement would be a better use than multiple IFs/nested IF statements. Google be your best bet to find out more about these, then see if you can apply?

 

[stefankemp]

When you say "it doesn't work properly" what do you mean? What happens?

For readability sake I suggest the following:

dim low as boolean, medium as boolean, high as boolean

low = (range("AH4") = true)
medium = (range("AH3") = true)
high = (range("AH2") = true)
if high and medium and low then
...
if not(high) and medium and low then
...
etc.

 

[jimrward]

ok 3 things spotted so far

1) no end if to close off the statement

2) spelling mistake in trues

3) no criteria, when all cells are FALSE

 

[JimSnyder]

Combine the two answers directly after your question with something like this (UNTESTED)

Select Case Range()
Case Range("AH2") = True And Range("AH3") = True And Range("AH4") = True
Selection.AutoFilter Field:=25 'Note : All
Case Range("AH2") = False And Range("AH3") = True And Range("AH4") = True
Selection.AutoFilter Field:=25, Criteria1:="<>High" 'Note : Medium and Low
Case Range("AH2") = False And Range("AH3") = False And Range("AH4") = True
Selection.AutoFilter Field:=25, Criteria1:="<>High", Operator:=xlAnd, _
Criteria2:="<>Medium" 'Note : Low only
Case Range("AH2") = False And Range("AH3") = False And Range("AH4") = False
Selection.AutoFilter Field:=25, Criteria1:="=" 'Note: None Selected
Case Range("AH2") = True And Range("AH3") = False And Range("AH4") = True
Selection.AutoFilter Field:=25, Criteria1:="<>Medium" 'Note : High and Low
Case Range("AH2") = True And Range("AH3") = False And Range("AH4") = False
Selection.AutoFilter Field:=25, Criteria1:="<>Medium", Operator:=xlAnd, _
Criteria2:="<>Low" 'Note : High only
Case Range("AH2") = True And Range("AH3") = True And Range("AH4") = False
Selection.AutoFilter Field:=25, Criteria1:="<>Low" 'Note : High and Medium
Case Range("AH2") = False And Range("AH3") = True And Range("AH4") = False
Selection.AutoFilter Field:=25, Criteria1:="<>High", Operator:=xlAnd, _
Criteria2:="<>Low" 'Note : Medium only
Case Else
' Do nothing or generate an error message...
End Select

 

[jimrward]

using IF's but in a different way, i bet it will lose the indent when I post, so added - to pad out, every 4 hypens = i tab

If Range("AH2") = True Then
----If Range("AH3") = True Then
--------If Range("AH4") = True Then
------------Selection.AutoFilter Field:=25 'Note : All
--------Else
------------Selection.AutoFilter Field:=25, Criteria1:="<>Low" 'Note : High and Medium
--------End If
----Else
--------If Range("AH4") = True Then
------------Selection.AutoFilter Field:=25, Criteria1:="<>Medium" 'Note : High and Low
--------Else
------------Selection.AutoFilter Field:=25, Criteria1:="<>Medium", Operator:=xlAnd, Criteria2:="<>Low" 'Note : High only
--------End If
----End If
Else
----If Range("AH3") = True Then
--------If Range("AH4") = True Then
------------Selection.AutoFilter Field:=25, Criteria1:="<>High" 'Note : Medium and Low
--------Else
------------Selection.AutoFilter Field:=25, Criteria1:="<>High", Operator:=xlAnd, Criteria2:="<>Low" 'Note : Medium only
--------End If
----Else
--------If Range("AH4") = True Then
------------Selection.AutoFilter Field:=25, Criteria1:="<>High", Operator:=xlAnd, Criteria2:="<>Medium" 'Note : Low only
--------Else
------------Selection.AutoFilter Field:=25, Criteria1:="=" 'Note: None Selected
--------End If
----End If
End If

 

[drew.j.harrison]

Looks like I was able to get it to work. I tried using the select case but couldn't get it to work properly. It turns out I wasn't far off. I think it was just the one misspelling of True that I had. Here is the final code. (BTW I did have a "End If" I had just accidentally cut it off when I posted)

If (Range("AH2") = True) And (Range("AH3") = True) And (Range("AH4") = True) Then
Selection.AutoFilter Field:=25 'Note : All
ElseIf (Range("AH2") = False) And (Range("AH3") = False) And (Range("AH4") = False) Then
Selection.AutoFilter Field:=25, Criteria1:="=" 'Note: None Selected
ElseIf (Range("AH2") = True) And (Range("AH3") = False) And (Range("AH4") = False) Then
Selection.AutoFilter Field:=25, Criteria1:="<>Medium", Operator:=xlAnd, Criteria2:="<>Low" 'Note : High only
ElseIf (Range("AH2") = False) And (Range("AH3") = True) And (Range("AH4") = False) Then
Selection.AutoFilter Field:=25, Criteria1:="<>High", Operator:=xlAnd, Criteria2:="<>Low" 'Note : Medium only
ElseIf (Range("AH2") = False) And (Range("AH3") = False) And (Range("AH4") = True) Then
Selection.AutoFilter Field:=25, Criteria1:="<>High", Operator:=xlAnd, Criteria2:="<>Medium" 'Note : Low only
ElseIf (Range("AH2") = False) And (Range("AH3") = True) And (Range("AH4") = True) Then
Selection.AutoFilter Field:=25, Criteria1:="<>High" 'Note : Medium and Low
ElseIf (Range("AH2") = True) And (Range("AH3") = False) And (Range("AH4") = True) Then
Selection.AutoFilter Field:=25, Criteria1:="<>Medium" 'Note : High and Low
ElseIf (Range("AH2") = True) And (Range("AH3") = True) And (Range("AH4") = False) Then
Selection.AutoFilter Field:=25, Criteria1:="<>Low" 'Note : High and Medium
End If

Thanks again for all the help guys.

 

[MrKowz]

using IF's but in a different way, i bet it will lose the indent when I post, so added - to pad out, every 4 hypens = i tab

If you use CODE tags, it will not only retain indention, but also reduce the physical size of the post on the page by allowing to scroll through the code (very very nice for the LONG bits).

To use them, after you paste code into the forum, highlight the code and press the # symbol in the post's toolbar. Or, alternatively manually put the tags in so it reads like:

[CODE]
'your code goes here
'code continues
'stuff
[/CODE]

So with the above code (with the hyphens), it would look like this after properly using code tags:

If Range("AH2") = True Then
    If Range("AH3") = True Then
        If Range("AH4") = True Then
            Selection.AutoFilter Field:=25 'Note : All
        Else
            Selection.AutoFilter Field:=25, Criteria1:="<>Low" 'Note : High and Medium
        End If
    Else
        If Range("AH4") = True Then
            Selection.AutoFilter Field:=25, Criteria1:="<>Medium" 'Note : High and Low
        Else
            Selection.AutoFilter Field:=25, Criteria1:="<>Medium", Operator:=xlAnd, Criteria2:="<>Low" 'Note : High only
        End If
    End If
Else
    If Range("AH3") = True Then
        If Range("AH4") = True Then
            Selection.AutoFilter Field:=25, Criteria1:="<>High" 'Note : Medium and Low
        Else
            Selection.AutoFilter Field:=25, Criteria1:="<>High", Operator:=xlAnd, Criteria2:="<>Low" 'Note : Medium only
        End If
    Else
        If Range("AH4") = True Then
            Selection.AutoFilter Field:=25, Criteria1:="<>High", Operator:=xlAnd, Criteria2:="<>Medium" 'Note : Low only
        Else
            Selection.AutoFilter Field:=25, Criteria1:="=" 'Note: None Selected
        End If
    End If
End If

 

[drew.j.harrison]

using IF's but in a different way, i bet it will lose the indent when I post, so added - to pad out, every 4 hypens = i tab

If Range("AH2") = True Then
----If Range("AH3") = True Then
--------If Range("AH4") = True Then
------------Selection.AutoFilter Field:=25 'Note : All
--------Else
------------Selection.AutoFilter Field:=25, Criteria1:="<>Low" 'Note : High and Medium
--------End If
----Else
--------If Range("AH4") = True Then
------------Selection.AutoFilter Field:=25, Criteria1:="<>Medium" 'Note : High and Low
--------Else
------------Selection.AutoFilter Field:=25, Criteria1:="<>Medium", Operator:=xlAnd, Criteria2:="<>Low" 'Note : High only
--------End If
----End If
Else
----If Range("AH3") = True Then
--------If Range("AH4") = True Then
------------Selection.AutoFilter Field:=25, Criteria1:="<>High" 'Note : Medium and Low
--------Else
------------Selection.AutoFilter Field:=25, Criteria1:="<>High", Operator:=xlAnd, Criteria2:="<>Low" 'Note : Medium only
--------End If
----Else
--------If Range("AH4") = True Then
------------Selection.AutoFilter Field:=25, Criteria1:="<>High", Operator:=xlAnd, Criteria2:="<>Medium" 'Note : Low only
--------Else
------------Selection.AutoFilter Field:=25, Criteria1:="=" 'Note: None Selected
--------End If
----End If
End If

This is the much cleaner way of doing it. I thought about doing nesting like this but decided to just create all possible circumstances since it was only 3 variables and errors in individual lines we're less likely to result in all of them not working. Thanks again.

 

[Gary McMaster]

I assume you are using linked cells for your check box status.

A clean alternative would be to bit code the status of the check boxes right in the check box click event. Then you could use Select Case to identify the 8 possible combinations as others have suggested.

Gary


Check box click event(s):

Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
    iBitCode = iBitCode Or 1
Else
    iBitCode = iBitCode And 6
End If

ReadBitCode

End Sub

Private Sub CheckBox2_Click()

If CheckBox2.Value = True Then
    iBitCode = iBitCode Or 2
Else
    iBitCode = iBitCode And 5
End If

ReadBitCode

End Sub

Private Sub CheckBox3_Click()

If CheckBox3.Value = True Then
    iBitCode = iBitCode Or 4
Else
    iBitCode = iBitCode And 3
End If

ReadBitCode

End Sub

Module code:

Option Explicit

'Initialize iBitCode (below) to 0 at startup for nothing checked
'Initialize iBitCode (below) to 7 at startup for all three checked

Public iBitCode As Integer

Public Sub ReadBitCode()

Select Case iBitCode
    Case 0
        MsgBox "None Checked"
    Case 1
        MsgBox "No 1 Only"
    Case 2
        MsgBox "No 2 Only"
    Case 3
        MsgBox "No 1 & No 2"
    Case 4
        MsgBox "No 3 Only"
    Case 5
        MsgBox "No 1 & No 3"
    Case 6
        MsgBox "No 2 & No 3"
    Case 7
        MsgBox "All 3 Checked"
End Select

End Sub