compare 3 ranges instead of 2

Question

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

This UDF compares 2 ranges and return the number of matched values. I would like to compare 3 ranges instead, in order to find how many values appeared in all 3 ranges simultaneously.

Much appreciate any help.

Answer 1

Public Function Compare(r1 As Range, r2 As Range, r3 As Range) As Long
   Dim r As Range, v As Variant, m1 As Variant, m2 As Variant
   Dim rv As Long

   rv = 0
   For Each r In r1
      v = r.Value
      If v <> 0 And v <> "" And Not IsError(v) Then
            m1 = Application.Match(v, r2, 0)
            m2 = Application.Match(v, r3, 0)
            If Not IsError(m1) And Not IsError(m2) Then
               rv = rv + 1
            End If
      End If
   Next r
   Compare = rv
End Function