VBA - How to find words from list in a cell?

[Fixed]

Hello!

I have the function:

IF(SUMPRODUCT(--ISNUMBER(SEARCH(E1:E2,A1)));"yes","no")

that searches the text values from E1:E2 in A1 cell.
How to make the same in VBA?

Thanks in advance!

 

[Rick Rothstein]

When E1 or E2 is empty, the functions say "yes" instead of "no".

That is what the formula you posted in Message #1 did and you asked us to make VBA do what your formula did.

When E1 or E2 is empty, the functions say "yes" instead of "no".
How to find the non-empty values only?

Try this revised version of the function that I posted earlier...

[table="width: 500"]
[tr]
	[td]Function FindTxts(SingleCell As Range, RangeOfCells As Range) As Variant
  Dim Cel As Range
  For Each Cel In RangeOfCells
    FindTxts = FindTxts + (Cel.Value <> 0 And InStr(1, SingleCell, Cel.Value, vbTextCompare) > 0)
  Next
  If FindTxts Then FindTxts = "Yes" Else FindTxts = "No"
End Function[/td]
[/tr]
[/table]

 

[Fixed]

That is what the formula you posted in Message [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1 [/URL] did and you asked us to make VBA do what your formula did.

I know that your function works as an example from Meassage #1 , but I didn't think about it before, sorry.

Try this revised version of the function that I posted earlier...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function FindTxts(SingleCell As Range, RangeOfCells As Range) As Variant
   Dim Cel As Range
   For Each Cel In RangeOfCells
     FindTxts = FindTxts + (Cel.Value <> 0 And InStr(1, SingleCell, Cel.Value, vbTextCompare) > 0)
   Next
   If FindTxts Then FindTxts = "Yes" Else FindTxts = "No"
 End Function[/TD]
[/TR]
</tbody>[/TABLE]

The that one works with empty values perfectly, thank you!