Select Case Not Or

Question

I'm looking to carry out a Select Case with just one case - where the case is not equal to "P", "Ev" or "Af".

This is what I have so far.

Select Case Range("my_range").Offset(0, column_offset).Value Case Not "P", "Ev", "Af" 'my code End Select

The case could equal 50 different values and I want the same action (under 'my code) to be performed for all of them unless the result is P, Ev or Af.

I've also tried Not "P", Not "Ev", Not "Af" along with replacing , with Or but to no avail.

The response each and every time is:

Run-time error '13': Type mismatch.

I know I could replace this with an if statement along the lines of...

If Range("my_range").Offset(0, column_offset).Value <> "P" And Range("my_range").Offset(0, column_offset).Value <> "Ev" And Range("my_range").Offset(0, column_offset).Value <> "Af" Then 'my code End if

but I'd prefer to use the Select Case option if I can.

Any thoughts anyone?

Many thanks

EDIT

I should also say I did try using

Select Case Range("my_range").Offset(0, column_offset).Value Case "P", "Ev", "Af" Exit Select Case Else 'my code End Select

but the error message:

Compile error: Expected: Do or For or Sub or Function or Property

kept popping up.

Bathsheba · Accepted Answer · 2014-04-10 11:57:43Z

16

You cannot use Not in this way. But you can refactor to

Select Case Range("my_range").Offset(0, column_offset).Value Case "P", "Ev", "Af" 'ignore this Case Else 'my code End Select

answered Apr 10, 2014 at 11:57

Bathsheba

235k35 gold badges376 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tom_j_uk Over a year ago

I have just added an edit that says that I tried to add add Exit Select (where you've written 'ignore this) which didn't work, but of course all I need is to just not enter code and the next line will move on and exit the select! Clearly one of those days...! Thanks for your help. :)

SweDentan · Accepted Answer · 2020-07-22 11:48:08Z

I know this question were asked a long time ago, but today I stumbled across the exact same wonder: -Could you use a Select Case Statment with a Not Operator?

Neither have I found that Not can be used, But instead this method could be used:

Select Case [variable] Case is <> [condition 1] 'my code End Select

Collectives™ on Stack Overflow

Select Case Not Or

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related