Hello:

How to make this formula based on "contains" rather than "equals"? My
attempts are not working:

With Range("[range]")
.FormulaR1C1 = "=(OR(RC[-1]={""Text1"",""Text2"", ""Text3"})"
End With

I have a string and I need to know which "contain" either of the three
texts. I've played around with mid and instr with no success.

Thanks, Germain

one way:

With Range("rng")
.FormulaR1C1 = _
"=SUM(COUNTIF(RC[-1],{""Text1"",""Text2"", ""Text3""}))0"
End With

or, if Text1 can appear anywhere in the cell:

With Range("rng")
.FormulaR1C1 = "=SUM(COUNTIF(RC[-1]," & _
"{""*Text1*"",""*Text2*"", ""*Text3*""}))0"
End With

In article ,
"Bettergains" wrote:

Hello:

How to make this formula based on "contains" rather than "equals"? My
attempts are not working:

With Range("[range]")
.FormulaR1C1 = "=(OR(RC[-1]={""Text1"",""Text2"", ""Text3"})"
End With

I have a string and I need to know which "contain" either of the three
texts. I've played around with mid and instr with no success.

Thanks, Germain

Thanks, Jack.
It works beautifully. The second was the solution I was after. The original
I posted works if the string is equivalent to a complete, discreet text (as
in "Text1"); the second you posted solves the "contains" query I was
attempting (string contains "Text1" or "Text2", etc.)

Your help always appreciated.

Germaine

"JE McGimpsey" wrote:

one way:

With Range("rng")
.FormulaR1C1 = _
"=SUM(COUNTIF(RC[-1],{""Text1"",""Text2"", ""Text3""}))0"
End With

or, if Text1 can appear anywhere in the cell:

With Range("rng")
.FormulaR1C1 = "=SUM(COUNTIF(RC[-1]," & _
"{""*Text1*"",""*Text2*"", ""*Text3*""}))0"
End With

In article ,
"Bettergains" wrote:

Hello:

How to make this formula based on "contains" rather than "equals"? My
attempts are not working:

With Range("[range]")
.FormulaR1C1 = "=(OR(RC[-1]={""Text1"",""Text2"", ""Text3"})"
End With

I have a string and I need to know which "contain" either of the three
texts. I've played around with mid and instr with no success.

Thanks, Germain