Can I use "or" in a select case construct?

[broro183]

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...

[Helmut Weber]

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"

[broro183]

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...

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[broro183]

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...

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[broro183]

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...