Select Case syntax for multiple conditions ("and")?

K

ker_01

I have a series of values (percentages) to process through a select case
statement. Cases include:
.2 ' more than 20%
.02 AND <=.2 ' between 2% and 20%
-.0199 AND <.0199 ' between +/-2%
-.0499 AND <-.0199 ' between -5% and -2%
Click to expand...
etc.

I'm having trouble figuring out the syntax for a case range statement. I
thought I had it with
Select Case MyVar
Case (MyVar >.2)
'do stuff
Case (MyVar >.02) AND (MyVar <=.2)
'do stuff
Case (MyVar >-.02) AND (MyVar<=.02)

but that isn't working.

What is the proper way to use a range of values that includes [greater than]
and [less than or equal to] as an AND criteria?

Thank you!
Keith
 
B

Brandt

ker_01,

Per the vba help file: "If testexpression matches an expressionlist
expression in more than one Case clause, only the statements following the
first match are executed." Therfore, you dont need to worry about the "and"
statement because the second half of it (upperbound in your case) has
already been checked by the previous statement.

For example (not tested):

Select Case MyVar
Case MyVar > 0.2
'do stuff
Case MyVar >0.02 ' if we got this far we already know MyVar <= 0.2
'do stuff
Case MyVar > -0.02 'Only get to this point if MyVar <= 0.02
'do stuff
Case Else
'do default stuff
End Select

Hope this helps
Brandt

ker_01 said:
I have a series of values (percentages) to process through a select case
statement. Cases include:
.2 ' more than 20%
.02 AND <=.2 ' between 2% and 20%
-.0199 AND <.0199 ' between +/-2%
-.0499 AND <-.0199 ' between -5% and -2%
Click to expand...
etc.

I'm having trouble figuring out the syntax for a case range statement. I
thought I had it with
Select Case MyVar
Case (MyVar >.2)
'do stuff
Case (MyVar >.02) AND (MyVar <=.2)
'do stuff
Case (MyVar >-.02) AND (MyVar<=.02)

but that isn't working.

What is the proper way to use a range of values that includes [greater than]
and [less than or equal to] as an AND criteria?

Thank you!
Keith
Click to expand...
 
C

Chip Pearson

For example (not tested):

Select Case MyVar
Case MyVar > 0.2
Click to expand...

Very definitely not tested. It doesn't work as you think. You are
confusing and combining two different approaches to a Select Case
statement.

You want EITHER

MyVar = 0.15
Select Case True ' compare to Boolean True (= -1)
Case MyVar > 0.2
Debug.Print "a"
Case MyVar > 0.02 ' if we got this far we already know MyVar <= 0.2
Debug.Print "b"
Case MyVar > -0.02 'Only get to this point if MyVar <= 0.02
Debug.Print "c"
Case Else
Debug.Print "d"
End Select


OR


MyVar = 0.15
Select Case MyVar ' compare to value of MyVar
Case Is > 0.2
Debug.Print "a"
Case Is > 0.02
Debug.Print "b"
Case Is > -0.02
Debug.Print "c"
Case Else
Debug.Print "d"
End Select


In the first example, there is a comparison on each Case clause, and
since a comparison returns only either True ( = -1) or False (= 0),
you need either True or False in the Select variable.

In the second example, each Case clause uses the Is operator, so the
Select variable is the value of MyVar.

Choose either approach, but don't mix them up.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





ker_01,

Per the vba help file: "If testexpression matches an expressionlist
expression in more than one Case clause, only the statements following the
first match are executed." Therfore, you dont need to worry about the "and"
statement because the second half of it (upperbound in your case) has
already been checked by the previous statement.

For example (not tested):

Select Case MyVar
Case MyVar > 0.2
'do stuff
Case MyVar >0.02 ' if we got this far we already know MyVar <= 0.2
'do stuff
Case MyVar > -0.02 'Only get to this point if MyVar <= 0.02
'do stuff
Case Else
'do default stuff
End Select

Hope this helps
Brandt

ker_01 said:
I have a series of values (percentages) to process through a select case
statement. Cases include:
.2 ' more than 20%
.02 AND <=.2 ' between 2% and 20%
-.0199 AND <.0199 ' between +/-2%
-.0499 AND <-.0199 ' between -5% and -2%
Click to expand...
etc.

I'm having trouble figuring out the syntax for a case range statement. I
thought I had it with
Select Case MyVar
Case (MyVar >.2)
'do stuff
Case (MyVar >.02) AND (MyVar <=.2)
'do stuff
Case (MyVar >-.02) AND (MyVar<=.02)

but that isn't working.

What is the proper way to use a range of values that includes [greater than]
and [less than or equal to] as an AND criteria?

Thank you!
Keith
Click to expand...
Click to expand...
 
K

ker_01

<hangs head>
It's been a long week, that's the only excuse I have for not realizing that.

Thanks!

Brandt said:
ker_01,

Per the vba help file: "If testexpression matches an expressionlist
expression in more than one Case clause, only the statements following the
first match are executed." Therfore, you dont need to worry about the "and"
statement because the second half of it (upperbound in your case) has
already been checked by the previous statement.

For example (not tested):

Select Case MyVar
Case MyVar > 0.2
'do stuff
Case MyVar >0.02 ' if we got this far we already know MyVar <= 0.2
'do stuff
Case MyVar > -0.02 'Only get to this point if MyVar <= 0.02
'do stuff
Case Else
'do default stuff
End Select

Hope this helps
Brandt

ker_01 said:
I have a series of values (percentages) to process through a select case
statement. Cases include:
.2 ' more than 20%
.02 AND <=.2 ' between 2% and 20%
-.0199 AND <.0199 ' between +/-2%
-.0499 AND <-.0199 ' between -5% and -2%
Click to expand...
etc.

I'm having trouble figuring out the syntax for a case range statement. I
thought I had it with
Select Case MyVar
Case (MyVar >.2)
'do stuff
Case (MyVar >.02) AND (MyVar <=.2)
'do stuff
Case (MyVar >-.02) AND (MyVar<=.02)

but that isn't working.

What is the proper way to use a range of values that includes [greater than]
and [less than or equal to] as an AND criteria?

Thank you!
Keith
Click to expand...
Click to expand...
 
G

Gord Dibben

Have a play with this. I don't know if I got the ranges quite right.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is > 0.2: Num = 10 'green
Case 0.02 To 0.2: Num = 1 'black
Case -0.0199 To 0.0199: Num = 5 'blue
Case -0.0499 To -0.0199: Num = 7 'magenta
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
End Sub


Gord Dibben MS Excel MVP

I have a series of values (percentages) to process through a select case
statement. Cases include:
.2 ' more than 20%
.02 AND <=.2 ' between 2% and 20%
-.0199 AND <.0199 ' between +/-2%
-.0499 AND <-.0199 ' between -5% and -2%
Click to expand...
etc.

I'm having trouble figuring out the syntax for a case range statement. I
thought I had it with
Select Case MyVar
Case (MyVar >.2)
'do stuff
Case (MyVar >.02) AND (MyVar <=.2)
'do stuff
Case (MyVar >-.02) AND (MyVar<=.02)

but that isn't working.

What is the proper way to use a range of values that includes [greater than]
and [less than or equal to] as an AND criteria?

Thank you!
Keith
Click to expand...
 
B

Brandt

Thanks Chip,

You are very correct! I always have to fight with Select Case statements
when I use them for this reason. I was trying to get a concept across, not
actual code, and hence the "not tested" disclaimer. This is a poor excuse, I
know, but it is the only one I have :)

Brandt

Chip Pearson said:
For example (not tested):

Select Case MyVar
Case MyVar > 0.2
Click to expand...

Very definitely not tested. It doesn't work as you think. You are
confusing and combining two different approaches to a Select Case
statement.

You want EITHER

MyVar = 0.15
Select Case True ' compare to Boolean True (= -1)
Case MyVar > 0.2
Debug.Print "a"
Case MyVar > 0.02 ' if we got this far we already know MyVar <= 0.2
Debug.Print "b"
Case MyVar > -0.02 'Only get to this point if MyVar <= 0.02
Debug.Print "c"
Case Else
Debug.Print "d"
End Select


OR


MyVar = 0.15
Select Case MyVar ' compare to value of MyVar
Case Is > 0.2
Debug.Print "a"
Case Is > 0.02
Debug.Print "b"
Case Is > -0.02
Debug.Print "c"
Case Else
Debug.Print "d"
End Select


In the first example, there is a comparison on each Case clause, and
since a comparison returns only either True ( = -1) or False (= 0),
you need either True or False in the Select variable.

In the second example, each Case clause uses the Is operator, so the
Select variable is the value of MyVar.

Choose either approach, but don't mix them up.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





ker_01,

Per the vba help file: "If testexpression matches an expressionlist
expression in more than one Case clause, only the statements following the
first match are executed." Therfore, you dont need to worry about the "and"
statement because the second half of it (upperbound in your case) has
already been checked by the previous statement.

For example (not tested):

Select Case MyVar
Case MyVar > 0.2
'do stuff
Case MyVar >0.02 ' if we got this far we already know MyVar <= 0.2
'do stuff
Case MyVar > -0.02 'Only get to this point if MyVar <= 0.02
'do stuff
Case Else
'do default stuff
End Select

Hope this helps
Brandt

ker_01 said:
I have a series of values (percentages) to process through a select case
statement. Cases include:
.2 ' more than 20%
.02 AND <=.2 ' between 2% and 20%
-.0199 AND <.0199 ' between +/-2%
-.0499 AND <-.0199 ' between -5% and -2%
etc.

I'm having trouble figuring out the syntax for a case range statement. I
thought I had it with
Select Case MyVar
Case (MyVar >.2)
'do stuff
Case (MyVar >.02) AND (MyVar <=.2)
'do stuff
Case (MyVar >-.02) AND (MyVar<=.02)

but that isn't working.

What is the proper way to use a range of values that includes [greater than]
and [less than or equal to] as an AND criteria?

Thank you!
Keith
Click to expand...
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

select case syntax to other sheets 5
counting with multiple conditions 8
Select Case code error 8
Select case question 16
Select case syntax for continuous range values? 1
Select Case 4
select case error 4
select case multiple conditions 2

Top