Test if a Cell contains either of 12 different words
Evaluate("SUM(COUNTIF(" & ActiveCell.Address &
",{""*APPLE*"",""*BUT*"",""*CAR*"",""*DAM*"",""*EX CEL*"",
""*FOR*"",""*GO*"",""*HI*"",""*AND*"",""*THE*"","" *DOG*"",""*OR*""}))")
--
Regards,
Tom Ogilvy
"J@Y" wrote in message
...
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?
"Tom Ogilvy" wrote:
double quotes embedded in a string must be doubled to be interpreted as a
double quote character and not the string delimiter.
--
Regards,
Tom Ogilvy
"J@Y" wrote:
I see. Also, why do you use ""*Car*"" with double quotes instead of
"*Car*"
which works normally for Countif statements?
"Tom Ogilvy" wrote:
It evaluates the string passed to it as if it was entered in a cell
in the
worksheet.
--
Regards,
Tom Ogilvy
"J@Y" wrote:
What exactly does the evaluate do?
"Tom Ogilvy" wrote:
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
--
Regards,
Tom Ogilvy
"J@Y" wrote:
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 *.
"steve_doc" wrote:
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
"J@Y" wrote:
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.
|