Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use "or" in a select case construct?
Hi all, As part of a larger macro which performs custom autofiltering using shortcut key I am trying to use an "Or" operator in one of my "case is lines. Currently I have the following code which is used after identifyin that the left character of the "InitialFilterValue" (dimmed as a strin & sourced from an input box) is "<". Select Case Left(InitialFilterValue, 2) Case Is = "<" GoTo MakeStringPrefixDoubleLeft Case Is = "<=" GoTo MakeStringPrefixDoubleLeft Case Else GoTo MakeStringPrefixSingleLeft End Select In the interests of optimising my code & because the first two case have the same action, is it possible to combine the first two case using something like... " Case is = "<" Or "<=" "? FYI, when I try the above line I get the error message "Run time error 13 type mismatch". I've dimmed InitialFilterValue as a string becaus there will be other situations when I am attempting to filter tex strings. Also in the interests of optimising my complete code is anyon interested in reviewing it & making suggestions? btw, it is not as complete as Ron DeBruin's "easy filter http://www.rondebruin.nl/easyfilter.htm but it is quick & effective (apart from dates, which can stil sometimes cause issues) wrt what I need - a keyboard activated custo filter. I'll try any suggestions when I get a chance in the morning, Thanks in advance, Rob Brockett NZ Always learning & the best way to learn is to experience.. -- broro18 ----------------------------------------------------------------------- broro183's Profile: http://www.excelforum.com/member.php...fo&userid=3006 View this thread: http://www.excelforum.com/showthread.php?threadid=51909 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use "or" in a select case construct?
Hi,
you don't use "is" here. I cant't explain all that there is to "is", and I doubt if I would be able to. Use a comma for "or", like: Dim s As String s = "<" Select Case s Case "<=", "<": MsgBox "< or <=" End Select -- Greetings from Bavaria, Germany Helmut Weber, MVP WordVBA Win XP, Office 2003 "red.sys" & Chr$(64) & "t-online.de" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use "or" in a select case construct?
Hi Helmut, Thankyou, for the quick response - it works well. Vielen Dank für Ihre Hilfe (courtesy of http://babelfish.altavista.com/tr) :-) Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=519094 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use "or" in a select case construct?
Select Case Left(InitialFilterValue, 2)
Case "<", "<=" GoTo MakeStringPrefixDoubleLeft Case Else GoTo MakeStringPrefixSingleLeft End Select -- HTH Bob Phillips (remove nothere from email address if mailing direct) "broro183" wrote in message ... Hi all, As part of a larger macro which performs custom autofiltering using a shortcut key I am trying to use an "Or" operator in one of my "case is" lines. Currently I have the following code which is used after identifying that the left character of the "InitialFilterValue" (dimmed as a string & sourced from an input box) is "<". Select Case Left(InitialFilterValue, 2) Case Is = "<" GoTo MakeStringPrefixDoubleLeft Case Is = "<=" GoTo MakeStringPrefixDoubleLeft Case Else GoTo MakeStringPrefixSingleLeft End Select In the interests of optimising my code & because the first two cases have the same action, is it possible to combine the first two cases using something like... " Case is = "<" Or "<=" "? FYI, when I try the above line I get the error message "Run time error: 13 type mismatch". I've dimmed InitialFilterValue as a string because there will be other situations when I am attempting to filter text strings. Also in the interests of optimising my complete code is anyone interested in reviewing it & making suggestions? btw, it is not as complete as Ron DeBruin's "easy filter" http://www.rondebruin.nl/easyfilter.htm but it is quick & effective (apart from dates, which can still sometimes cause issues) wrt what I need - a keyboard activated custom filter. I'll try any suggestions when I get a chance in the morning, Thanks in advance, Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=519094 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use "or" in a select case construct?
Thanks Bob, Helmut couldn't explain - are you able to explain why it doesn't need the "case is" form, what does the "is" means & show me an example of when that form should be used? I can't understand the help file in Excel 2002 which states: "expressionlist-n Required if a Case appears. Delimited list of one or more of the following forms: expression, expression To expression, Is comparisonoperator expression. The To keyword specifies a range of values. If you use the To keyword, the smaller value must appear before To. Use the Is keyword with comparison operators (except Is and Like) to specify a range of values. If not supplied, the Is keyword is automatically inserted." Also in the interests of optimising my complete code (recently rebuilt & not completely tested yet), do you have any suggestions of where I should/could post it for others to review & make suggestions? TIA Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=519094 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use "or" in a select case construct?
Rob,
Do not forget the help file is only providing a singleton condition, you want multiple conditions in a single Case statement. The other thing to understand is that multiple conditions are evaluated separately, that is the connection is a logical OR not a logical AND, so your OR is redundant, Because of this , the format for multiple conditions is Case "<", "<=" " If used, the Is keyword precedes any comparison operator, not the any. So in this instance, the syntax is Case Is = "<", Is = "<=" " I just don't use them, that is why response was worded as I did. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "broro183" wrote in message ... Thanks Bob, Helmut couldn't explain - are you able to explain why it doesn't need the "case is" form, what does the "is" means & show me an example of when that form should be used? I can't understand the help file in Excel 2002 which states: "expressionlist-n Required if a Case appears. Delimited list of one or more of the following forms: expression, expression To expression, Is comparisonoperator expression. The To keyword specifies a range of values. If you use the To keyword, the smaller value must appear before To. Use the Is keyword with comparison operators (except Is and Like) to specify a range of values. If not supplied, the Is keyword is automatically inserted." Also in the interests of optimising my complete code (recently rebuilt & not completely tested yet), do you have any suggestions of where I should/could post it for others to review & make suggestions? TIA Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=519094 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use "or" in a select case construct?
Hi Bob, Thankyou, that makes sense to me. I think I'll follow your lead of "I just don't use them...". Cheers Rob brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=519094 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I count only lower case "x" and exclude upper case "X" | Excel Worksheet Functions | |||
Why Error Message "End Select without Select Case"? | Excel Programming | |||
SELECT CASE "Jan-03" confusion | Excel Programming | |||
Fix Code: Select Case and "Contains" selection | Excel Programming | |||
Problem with Select Case construct? | Excel Programming |