Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using SELECT...CASE with comparison operators?
Can I convert the following IF...THEN statements to a SELECT...CASE?
if wigets = 20 AND wigets < 200 then... if wigets =200 AND wigets < 400 then... if wigets = 400 AND wigets < 600 then... Excel help wasn't too helpful. I tried unsuccessfully to use TO and IS. I don't think I understand them very well. Thanks, Toby Erkson Oregon, USA Excel 2002 in Windows XP |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using SELECT...CASE with comparison operators?
Hi
=IF(wigets <20;"";CHOOSE(INT(wigets /200)+1,"A","B","C","D")) -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "Toby Erkson" wrote in message ... Can I convert the following IF...THEN statements to a SELECT...CASE? if wigets = 20 AND wigets < 200 then... if wigets =200 AND wigets < 400 then... if wigets = 400 AND wigets < 600 then... Excel help wasn't too helpful. I tried unsuccessfully to use TO and IS. I don't think I understand them very well. Thanks, Toby Erkson Oregon, USA Excel 2002 in Windows XP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using SELECT...CASE with comparison operators?
The example code didn't help: Dim Number Number = 8 ' Initialize variable. Select Case Number ' Evaluate Number. Case 1 To 5 ' Number between 1 and 5, inclusive. Debug.Print "Between 1 and 5" ' The following is the only Case clause that evaluates to True. Case 6, 7, 8 ' Number between 6 and 8. Debug.Print "Between 6 and 8" Case 9 To 10 ' Number is 9 or 10. Debug.Print "Greater than 8" Case Else ' Other values. Debug.Print "Not between 1 and 10" End Select You would substitute Widgets for Number in the above and adjust you range of numbers (utilize the syntax of Case 1 to 5) -- Regards, Tom Ogilvy "Toby Erkson" wrote in message ... Can I convert the following IF...THEN statements to a SELECT...CASE? if wigets = 20 AND wigets < 200 then... if wigets =200 AND wigets < 400 then... if wigets = 400 AND wigets < 600 then... Excel help wasn't too helpful. I tried unsuccessfully to use TO and IS. I don't think I understand them very well. Thanks, Toby Erkson Oregon, USA Excel 2002 in Windows XP |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using SELECT...CASE with comparison operators?
Great story, Tom.
-----Original Message----- Try ... Select Case wigets Case 20 To 199 MsgBox "20 To 199" Case 200 To 399 MsgBox "200 To 399" Case 400 To 599 MsgBox "400 To 599" Case Else MsgBox "Else" End Select BTW, the correct spelling is wiDgets ;-) The story I heard was that it resulted from a mispelling of Midgets published in a well respected newspaper many years ago. People assumed it was just something they had never heard of before, rather than believe a mispelling could ever appear in that newspaper. Newspapers have come a long way (down) since then. Tom Lavedas =========== "Toby Erkson" wrote: Can I convert the following IF...THEN statements to a SELECT...CASE? if wigets = 20 AND wigets < 200 then... if wigets =200 AND wigets < 400 then... if wigets = 400 AND wigets < 600 then... Excel help wasn't too helpful. I tried unsuccessfully to use TO and IS. I don't think I understand them very well. Thanks, Toby Erkson Oregon, USA Excel 2002 in Windows XP . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using SELECT...CASE with comparison operators?
You know, I knew that looked wrong but I couldn't quite figure out why
Thanks :-) "Tom Lavedas" wrote in message ... .... BTW, the correct spelling is wiDgets ;-) The story I heard was that it resulted from a mispelling of Midgets published in a well respected newspaper many years ago. People assumed it was just something they had never heard of before, rather than believe a mispelling could ever appear in that newspaper. Newspapers have come a long way (down) since then. Tom Lavedas |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using SELECT...CASE with comparison operators?
C'mon Tom, it was example code, I was trying to be generic. How could I have
made it more helpful -- what would you suggestion? Actually, your code comments were perfect :-) Thanks for the help! Arvi, I actually have a lot more conditions and resulting statements so your example isn't even feasible (which is why I asked for SELECT...CASE), however, thanks for the alternative simple solution :-) -- Toby Erkson Oregon, USA Excel 2002 in Windows XP "Tom Ogilvy" wrote in message ... The example code didn't help: Dim Number Number = 8 ' Initialize variable. Select Case Number ' Evaluate Number. Case 1 To 5 ' Number between 1 and 5, inclusive. Debug.Print "Between 1 and 5" ' The following is the only Case clause that evaluates to True. Case 6, 7, 8 ' Number between 6 and 8. Debug.Print "Between 6 and 8" Case 9 To 10 ' Number is 9 or 10. Debug.Print "Greater than 8" Case Else ' Other values. Debug.Print "Not between 1 and 10" End Select You would substitute Widgets for Number in the above and adjust you range of numbers (utilize the syntax of Case 1 to 5) -- Regards, Tom Ogilvy .... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using SELECT...CASE with comparison operators?
Aw crap...what would you "suggest", not "suggestion"?
"Toby Erkson" wrote in message ... C'mon Tom, it was example code, I was trying to be generic. How could I have made it more helpful -- what would you suggestion? .... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using SELECT...CASE with comparison operators?
Hi
"Toby Erkson" wrote in message ... C'mon Tom, it was example code, I was trying to be generic. How could I have made it more helpful -- what would you suggestion? Actually, your code comments were perfect :-) Thanks for the help! Arvi, I actually have a lot more conditions and resulting statements so your example isn't even feasible (which is why I asked for SELECT...CASE), however, thanks for the alternative simple solution :-) Actually, CHOOSE worksheet function can have up to 30 arguments, so you have 29 possible responses there. When there is more of them, then you can wrap several CHOOSE's into IF(,,), like: =IF(wigets<20,"",IF(wigets<5800,CHOOSE(....),IF(wi gets<11600,CHOOSE(....),CH OOSE(...)))) but probably this isn't very good solution. And then you can have a list of choices on some sheet, and then you can refer to range - like: =IF(wigets<20,"",INDEX(Choices!A1:A1000,INT(A1 /200)+1,1)) A bonus with this - user can easily edit the list of choices without any need for VBA -- Arvi Laanemets (Don't use my reply address - it's spam-trap) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparison Operators in formulas | Excel Discussion (Misc queries) | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
Comparison Operators on a Toolbar | Excel Discussion (Misc queries) | |||
Comparison Operators for IF statement. | Excel Worksheet Functions | |||
select case help please | Excel Programming |