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

drew.j.harrison

Board Regular
Joined
Jul 9, 2010
Messages
65
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 11, 2010
Messages
136
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

Well-known Member
Joined
Feb 24, 2003
Messages
1,792
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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

Board Regular
Joined
Feb 28, 2011
Messages
125

ADVERTISEMENT

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

Well-known Member
Joined
Feb 24, 2003
Messages
1,792
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
 
Last edited:

drew.j.harrison

Board Regular
Joined
Jul 9, 2010
Messages
65

ADVERTISEMENT

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

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

Code:
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

Board Regular
Joined
Jul 9, 2010
Messages
65
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

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
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):
Code:
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:
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
 

Forum statistics

Threads
1,141,849
Messages
5,708,973
Members
421,602
Latest member
jkpce1880

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top