R1C1 Helper Instr / Mid
 

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

R1C1 Helper Instr / Mid
 

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

R1C1 Helper Instr / Mid
 

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