| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case syntax for multiple conditions ("and")?
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case syntax for multiple conditions ("and")?
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case syntax for multiple conditions ("and")?
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case syntax for multiple conditions ("and")?
<hangs head
It's been a long week, that's the only excuse I have for not realizing that. Thanks! "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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case syntax for multiple conditions ("and")?
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 On Thu, 22 Oct 2009 15:16:01 -0700, 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select Case syntax for multiple conditions ("and")?
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Select Case "Procedure to large" Error | Excel Programming | |||
| "Control" plus "click" doesn't allow me to select multiple cells | New Users to Excel | |||
| Syntax to "OR" 3 "ISERROR" conditions | Excel Worksheet Functions | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| Why Error Message "End Select without Select Case"? | Excel Programming | |||
Linear Mode
