Case Statement

  • Thread starter Thread starter jlclyde
  • Start date Start date
J

jlclyde

Is there anyway to set up this code to use OR for a case statement or
a way to check multiple things without putting in another case is =?
the code below is a general idea of what I am trying to do.

Thanks,
Jay

E:
Select Case Range("A1") .value
Case is = "Jim" or "Bill"
Drk = 1
Case is = "Jill" or "Fred"
Drk = 2
case else
range("A`1")=Inputbox("this is not a valid name, please enter a valid
name and press enter.")
goto E
End Select
 
Select Case Range("A1").Value
Case "Jim", "Bill": Drk = 1
Case "Jill", "Fred": Drk = 2
...
End Select



--
Jim
| Is there anyway to set up this code to use OR for a case statement or
| a way to check multiple things without putting in another case is =?
| the code below is a general idea of what I am trying to do.
|
| Thanks,
| Jay
|
| E:
| Select Case Range("A1") .value
| Case is = "Jim" or "Bill"
| Drk = 1
| Case is = "Jill" or "Fred"
| Drk = 2
| case else
| range("A`1")=Inputbox("this is not a valid name, please enter a valid
| name and press enter.")
| goto E
| End Select
 
You could do something like this. I've written it as a Sub that needs to be
called somehow.

Note that in VBA, "Bill" <> "BILL" nor does "Bill" = "bill", so you may need
to add some conversion to all uppercase or such to make sure that entries are
acceptabile or not.

Sub TestCase()
Dim testResult As Boolean
Dim Drk As Integer

Do While testResult = False
Select Case Worksheets("Sheet1").Range("A1")
Case Is = "John", "Jim", "Jack", "Bill"
Drk = 1
testResult = True
Case Is = "Ralph", "Mary", "Tobias"
Drk = 2
testResult = True
Case Else
Worksheets("Sheet1").Range("A1") = InputBox("Enter Valid Name", "Bad
Name", "Quit")
If Worksheets("Sheet1").Range("A1") = "Quit" Then
Exit Sub
End If
End Select
Loop
MsgBox "Drk = " & Drk
End Sub
 
I don't like GoTo's:

Option Explicit
Sub testme01()

Dim Drk As Long

Drk = 0
Do
Select Case Range("A1").Value
Case Is = "Jim", "Bill"
Drk = 1
Case Is = "Jill", "Fred"
Drk = 2
Case Else
Range("A1") = InputBox("this is not a valid name, " _
& "please enter a valid name and press enter.")
End Select
If Drk = 0 Then
'keep looking
Else
Exit Do
End If
Loop

MsgBox Drk

End Sub


And you don't give the user a way to cancel out of the loop???

And if case doesn't matter:

Select Case lcase(Range("A1").Value)
Case Is = lcase("Jim"), lcase("Bill")
Drk = 1
Case Is = lcase("Jill"), lcase("Fred")
 
Back
Top