Wildcards in a case statement

  • Thread starter Thread starter DG
  • Start date Start date
D

DG

I have a column that contains a 2 character code. How can I use only one
case statement to be true for all cases where the cell started with a C?
For example the cell could contain CA, CD, CQ, C5 etc.

Select Case Cells(x,9)
Case "BQ"
Rejected = False
Case "C*"
Rejected = True
End Select

Dan
 
In theory you could do something like this

Select case True
Case Left$(Cells(x, 9), 1) = "C"
' other Case's must evaluate to a boolean


However for your two conditions simply use If...ElseIf

s = Ucase(Cells(x,9)) ' assuming case insensitive
If s = "BQ" then

Elseif Left$(s,1) + "C" then

ElseIf blah then

End If

Regards,
Peter T
 
try this. change activecell to cells(x,9)

Sub iflefttext()
If UCase(Left(ActiveCell, 1)) = "C" Then
rejected = True
ElseIf UCase(ActiveCell) = "BQ" Then
rejected = False
End If
MsgBox rejected
End Sub
 
I'm nost sure what etc means here, but you may want to be explicit:

Dim x As Long
Dim Rejected As Boolean

x = 1
With ActiveSheet
Select Case UCase(.Cells(x, 9).Value)
'always use upper case here!
Case "BQ"
Rejected = False
Case "CA", "CD", "CQ", "C0" To "C5"
Rejected = True
End Select
End With
 
Back
Top