Comparing Cell Contents using a Case statement

I am writing a macro that will compare cell contents, using a case statement.
Does a method exist where a comparison can be made on a portion of the cell
contents? For example, I might want to act on a cell that contains the word
"apple". The caveat is that "apple" is not the only thing in the cell
string. I tried to use "like" and "is", but received a compile error -
apparently "like" and "is" are not valid comparison operators. What else
could I try?

if lcase(cell.Value) like "*apple*" then


from the immediate window:

ActiveCell.Value = "Johnny Appleseed"
? lcase(activecell.Value) Like "*apple*"
True
--
Regards,
Tom Ogilvy



"todd" <[email protected]> wrote in message
news:[email protected]...
> I am writing a macro that will compare cell contents, using a case
statement.
> Does a method exist where a comparison can be made on a portion of the
cell
> contents? For example, I might want to act on a cell that contains the
word
> "apple". The caveat is that "apple" is not the only thing in the cell
> string. I tried to use "like" and "is", but received a compile error -
> apparently "like" and "is" are not valid comparison operators. What else
> could I try?

This doesn't use a Select...Case, but it works and may get you going:

Private Function CellCompare()

Dim rCell As Range
Dim sCheck As String
sCheck = "Apple"
For Each rCell In ActiveSheet.UsedRange.Cells
If UCase(rCell.FormulaR1C1) Like "*" & UCase(sCheck) & "*" Then
MsgBox rCell.Address & " contains the word apple!"
End If
Next rCell
End Function

HTH/

"todd" wrote:

> I am writing a macro that will compare cell contents, using a case statement.
> Does a method exist where a comparison can be made on a portion of the cell
> contents? For example, I might want to act on a cell that contains the word
> "apple". The caveat is that "apple" is not the only thing in the cell
> string. I tried to use "like" and "is", but received a compile error -
> apparently "like" and "is" are not valid comparison operators. What else
> could I try?

Use a series of If...Then's instead. I.e., if your current comparison is:


Select Case foo
Case Is = "apple"
Msgbox "It's an apple"
Case Is = "pear"
Msgbox "It's a pear"
Case Else
Msgbox "It's some other fruit"
End Select

you can use

If foo Like "*apple*" Then
MsgBox "It's an apple"
ElseIf foo Like "*pear*" Then
MsgBox "It's a pear"
Else
MsgBox "It's some other frult"
End If

This has the disadvantage of evaluating the argument at each If/Elseif,
but it allows you to use the Like operator.


In article <[email protected]>,
"todd" <[email protected]> wrote:

> I am writing a macro that will compare cell contents, using a case statement.
> Does a method exist where a comparison can be made on a portion of the cell
> contents? For example, I might want to act on a cell that contains the word
> "apple". The caveat is that "apple" is not the only thing in the cell
> string. I tried to use "like" and "is", but received a compile error -
> apparently "like" and "is" are not valid comparison operators. What else
> could I try?

Sorry, didn't read clearly:

Sub ABC()
l = "Johhny Appleseed"
Select Case True
Case InStr(1, l, "apple", vbTextCompare)
MsgBox "apple"
Case InStr(1, l, "pear", vbTextCompare)
MsgBox "Pear"
End Select


End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> if lcase(cell.Value) like "*apple*" then
>
>
> from the immediate window:
>
> ActiveCell.Value = "Johnny Appleseed"
> ? lcase(activecell.Value) Like "*apple*"
> True
> --
> Regards,
> Tom Ogilvy
>
>
>
> "todd" <[email protected]> wrote in message
> news:[email protected]...
> > I am writing a macro that will compare cell contents, using a case
> statement.
> > Does a method exist where a comparison can be made on a portion of the
> cell
> > contents? For example, I might want to act on a cell that contains the
> word
> > "apple". The caveat is that "apple" is not the only thing in the cell
> > string. I tried to use "like" and "is", but received a compile error -
> > apparently "like" and "is" are not valid comparison operators. What
else
> > could I try?
>
>