PDA

View Full Version : Solved: Select Case with not an exact match



Djblois
09-01-2006, 12:35 PM
I am trying to use Select Case for lines of code that won't be exact. Here is my code

select case cells(i, 1).value
case "Atalanta"

However there may be characters after Atalanta. I tried adding a star after Atalanta and it doesn't work? Is this possible with Select Case?

Norie
09-01-2006, 12:49 PM
The short answer is no.:)

Cyberdude
09-01-2006, 01:08 PM
Have you looked at the "Like" operator? Review it in Help.

Zack Barresse
09-01-2006, 01:26 PM
I disagree. This of course depends on all of your criteria and code structure, which we have not seen. But for this one example only..

Select Case Left(Cells(i, 1).Value, 7)
Case "Atlanta"
MsgBox "Yup, it has atlanta"
End Select

There is another way by using a UDF in that you can, if you set all your desired search strings to an array (I'm assuming that you are looping, hence the row variable), use something like this...

Sub TestMyArray()
Dim i As Long, arrVals()
arrVals = Array("Atlanta", "Boston", "Portland", "Peoria", "Houston", "New York")
For i = LBound(arrVals) To UBound(arrVals)
Select Case InStr(1, Cells(i + 1, 3).Value, arrVals(i))
Case Is > 0
MsgBox "Yup, it has " & arrVals(i)
Case Else
MsgBox "Nope, it doesn't have " & arrVals(i)
End Select
Next i
End Sub

HTH

Zack Barresse
09-01-2006, 01:26 PM
CD, the Select statement will not take like. An If function will, or logical operation will, but not Select.

Norie
09-01-2006, 01:42 PM
Zack

I said the short answer was no, not that it wasn't possible.

But you can't just use a simple Select Case structure, as your code shows.

Further manipulation/code is required.

Zack Barresse
09-01-2006, 02:07 PM
Then you should have elaborated Norie. Short answers such as, "The short answer is no," really doesn't help anybody. Even if it was a helpful post, it certainly isn't true. The fact is that 99.9% of the time there is a perfectly good working solution to the query. It may not be what the OP is asking for, but there is a solution. What you did was led the OP to believe that there wasn't anything that would work here. I don't think that is correct.

johnske
09-01-2006, 06:11 PM
As mentioned above, an If statement will allow you to use wildcard characters (in conjunction with 'Like').

What you're after appears to be an ideal candidate for an If - ElseIf statement which has the following construct (and operates in a very similar manner to a Case statement) With Cells(i, 1)
If .Value Like "*atlanta" Then
'do something
ElseIf .Value Like "atlanta*" Then
'do something else
ElseIf .Value Like "*me*" Then
'do something else again
ElseIf .Value Like "*you*" Then
'
'ElseIf ... etc
'etc
'etc
'Else '< Optional
End If
End With

johnske
09-01-2006, 11:08 PM
Hi Zack,

I have to side with Norie here, the OPs only question was (Quote) "Is this possible with Select Case?". To which Norie simply gave the correct answer - No.

Regards,
John

Cyberdude
09-02-2006, 11:46 AM
Well, yeah, but ...
Sid

mvidas
09-03-2006, 05:55 PM
What about doing something like Dim strCity As String
strCity = "atlanta is a city in georgia" 'lcase(cells(i, 1).value)
Select Case True
Case strCity Like "atlanta*"
MsgBox "yep has atlanta"
Case strCity Like "macon*"
MsgBox "yep has macon"
End Select

johnske
09-03-2006, 06:45 PM
:clap: neat Matt...

mdmackillop
09-04-2006, 12:02 AM
What about doing something like Dim strCity As String
strCity = "atlanta is a city in georgia" 'lcase(cells(i, 1).value)
Select Case True
Case strCity Like "atlanta*"
MsgBox "yep has atlanta"
Case strCity Like "macon*"
MsgBox "yep has macon"
End Select
....which leads onto


strCity = "is atlanta a city in georgia?" 'lcase(cells(i, 1).value)

Case InStr(1, strCity, "atlanta") >= 1
MsgBox "yep has atlanta"

mvidas
09-05-2006, 07:02 AM
....which leads onto
strCity = "is atlanta a city in georgia?" 'lcase(cells(i, 1).value)

Case InStr(1, strCity, "atlanta") >= 1
MsgBox "yep has atlanta"

...which then leads to'...
Case InStr(strCity, "atlanta"): MsgBox "yep has atlanta" :)
Matt

mdmackillop
09-05-2006, 08:12 AM
Is it cheating to use a function?

Dim strCity As String, City As String
Sub hh()
strCity = "is atlanta a city in georgia" 'lcase(cells(i, 1).value)
City = "atlanta"
Select Case True
Case T: MsgBox "yep has " & City
Case Else: MsgBox "Not found"
End Select
End Sub

Function T()
T = InStr(strCity, City)
End Function

jungix
09-05-2006, 08:21 AM
Just to give my opinion on the answer of Norie: I am a newbie in VBA and I sometimes know how to code something using loops and workarounds but I ask if it possible in a direct way, in which case I try to be specific in my question. In this case an answer such as Norie's is useful, also further help such as Matt's is greatly appreciated for smarter workarounds ( I would just have looped and used the left function).