Test if a Cell contains either of 12 different words

[Guest]

I'm trying to test if a cell contains any of 12 different words (note the
words may be embedded in a phrase). Is there an easier way than to write 12
if statements?

Thanks.

 

[Guest]

Hi Joy

Try the Select Case statement

Not sure how to test with embedded text though - something for me to play
with and find out

HTH

 

[Guest]

The embedded text is what I'm having trouble with ... I tried using "*text*"
with Select Case and it doesnt work, the * are recognized literally as *.

 

[Guest]

if instr(1,cell.value,"abc",vbTextcompare) > 0 then
' contained in cell

 

[Guest]

In a worksheet, you can use something like:

=SUM(COUNTIF(B2,{"*APPLE*","*BUT*","*CAR*","*DAM*","*EXCEL*","*FOR*","*GO*","*HI*","*AND*","*THE*","*DOG*","*OR*"}))

this could be adapted to code as well.

Demo'd from the immediate window:

?
Evaluate("SUM(COUNTIF(B2,{""*APPLE*"",""*BUT*"",""*CAR*"",""*DAM*"",""*EXCEL*"",""*FOR*"",""*GO*"",""*HI*"",""*AND*"",""*THE*"",""*DOG*"",""*OR*""}))")
4
? Range("B2").Value
Go for an apple

 

[Guest]

What exactly does the evaluate do?

In a worksheet, you can use something like:

=SUM(COUNTIF(B2,{"*APPLE*","*BUT*","*CAR*","*DAM*","*EXCEL*","*FOR*","*GO*","*HI*","*AND*","*THE*","*DOG*","*OR*"}))

this could be adapted to code as well.

Demo'd from the immediate window:

?
Evaluate("SUM(COUNTIF(B2,{""*APPLE*"",""*BUT*"",""*CAR*"",""*DAM*"",""*EXCEL*"",""*FOR*"",""*GO*"",""*HI*"",""*AND*"",""*THE*"",""*DOG*"",""*OR*""}))")
4
? Range("B2").Value
Go for an apple

 

[Guest]

It evaluates the string passed to it as if it was entered in a cell in the
worksheet.

 

[Guest]

I see. Also, why do you use ""*Car*"" with double quotes instead of "*Car*"
which works normally for Countif statements?

 

[Guest]

double quotes embedded in a string must be doubled to be interpreted as a
double quote character and not the string delimiter.

 

[Guest]

I see. Thanks so much for the solution. One more quick question, since
Evaluate passes the string as if it was entered in the cell, how would I
refer to the cell (in this case B2) in the Countif statement with a cell
variable?

 

[Bob Phillips]

Because the quotes are within a quotes enclosed string, so you need to stop
them acting as ending and closing quotes. Doubling does that.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Tom Ogilvy]

Evaluate("SUM(COUNTIF(" & ActiveCell.Address &
",{""*APPLE*"",""*BUT*"",""*CAR*"",""*DAM*"",""*EXCEL*"",
""*FOR*"",""*GO*"",""*HI*"",""*AND*"",""*THE*"",""*DOG*"",""*OR*""}))")