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" wrote:


For example (not tested):

Select Case MyVar
Case MyVar 0.2

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)





On Thu, 22 Oct 2009 15:27:01 -0700, Brandt
wrote:

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" wrote:


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

.