Wildcards in a case statement

[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

 

[Peter T]

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

 

[Peter T]

typo!!

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

should of course read

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


Peter T

 

[Don Guillett]

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

 

[Dave Peterson]

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

 

[DG]

Thanks, I knew I could find it here.