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!

 

[footoo]

.

 

[Fixed]

Thank you, footoo​, I will try it!

 

[Fixed]

Function FindTxts(a As Range, r As Range) As Variant
Dim x$
Set r = [r].Find([a])
x = IIf(r Is Nothing, "No", "Yes")
FindTxts = x
End Function

It works, thank you!

 

[Fixed]

If a = a1, r = E1:E2 then I guess it searches A1 in E1:E2, but I need E1:E2 in A1.

 

[footoo]

Sub ft()
Dim rng As Range, s$, cel As Range, c%
Set rng = [E1:E2]
s = UCase([A1])
For Each cel In rng
    If InStr(1, s, UCase(cel)) <> 0 Then
        c = 1
        Exit For
    End If
Next
MsgBox IIf(c = 1, "Yes", "No")
End Sub

 

[Rick Rothstein]

See if this function works for you...

[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 + (InStr(1, SingleCell, Cel.Value, vbTextCompare) > 0)
  Next
  If FindTxts Then FindTxts = "Yes" Else FindTxts = "No"
End Function[/td]
[/tr]
[/table]

 

[Fixed]

Sub ft()
Dim rng As Range, s$, cel As Range, c%
Set rng = [E1:E2]
s = UCase([A1])
For Each cel In rng
    If InStr(1, s, UCase(cel)) <> 0 Then
        c = 1
        Exit For
    End If
Next
MsgBox IIf(c = 1, "Yes", "No")
End Sub

Now it works as I expected, thank you!

 

[Fixed]

See if this function works for you...

[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 + (InStr(1, SingleCell, Cel.Value, vbTextCompare) > 0)
  Next
  If FindTxts Then FindTxts = "Yes" Else FindTxts = "No"
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Great function, Rick, thank you!

 

[Fixed]

When E1 or E2 is empty, the functions say "yes" instead of "no".
How to find the non-empty values only?
I think I should use Ubound to find the last non-empty value, but I don't know how.
Please help me.
Thank you.