Compare two ranges: classic range vs range of comma-delimited values in a single cell

Question

Suppose I've got a range of values: 6 2 4 7 8 7 1 5 1 9 4 6 8 8 1 7 4 2

And separately in a single cell: "1, 4, 9"

I need to compare these values in a single cell with the given range. In case if there is a match - type "Yes", if not - "No".

"1, 4, 9" - result: "Yes"

"3, 10, 11" - result: "No"

Here I've got a UDF that simply compares two ordinary ranges and enters number of times values were matched as a result. Should work as a kind of a template I suppose.

 Public Function Compare(r1 As Range, r2 As Range) As Long
   Dim r As Range, v As Variant, v2 As Variant
   Dim rr As Range
   For Each r In r1
      v = r.Value
      If v <> 0 And v <> "" Then
         For Each rr In r2
            v2 = rr.Value
            If v = v2 Then Compare = Compare + 1
         Next rr
      End If
   Next r
End Function

Answer 1

I modified the code to split the first cell range:

 Public Function Compare(r1 As Range, r2 As Range) As Long
   Dim r As Integer, v As Variant, v2 As Variant
   Dim rr1() As String
   Dim rr As Range
   rr1 = Split(r1, ",")
   For r = LBound(rr1) To UBound(rr1)
      v = --Trim(rr1(r))
      If v <> 0 And v <> "" Then
         For Each rr In r2
            v2 = rr.Value
            If v = v2 Then Compare = Compare + 1
         Next rr
      End If
   Next r
End Function

To get the "Yes/No" you would call it like this:

=IF(Compare(A1,$D$1:$D$18)>0,"Yes","No")