Asked by:
Custom Excel Function to compare the data in two ranges and count unique values
Question

I have been trying to write a function to compare unique values, in order to see what recurrence there is in multiple data sets, and this is what I have.
Function CountUnique(range1 As Range, range2 As Range)
Dim TheCount As Long
TheCount = 0
Dim range3 As Variant, range4 As Variant
range3 = range1.Value
range4 = range2.Value
For i = 1 To UBound(range3)
For j = 1 To UBound(range4)
If range3(i, 1) = range4(j, 1) Then
TheCount = TheCount + 1
End If
Next j
Next i
CountUnique = TheCount
End FunctionThe code compiles fine but I am getting a #Value error back.
Thanks in advance for any help.
All replies

Try below code
Function CountUnique(range1 As Range, range2 As Range) Dim col As Collection Set col = New Collection Dim rng As Range, cell As Range Set rng = Union(range1, range2) On Error Resume Next For Each cell In rng If cell.Value <> "" Then col.Add cell.Value, cell.Value Next CountUnique = col.Count End Function Sub test() Dim rng1 As Range Set rng1 = Range("A1:A5") Dim rng2 As Range Set rng2 = Range("B1:B5") MsgBox CountUnique(rng1, rng2) End Sub





If your UDF is returning a #Value error it's because an error has occurred in the function, in this case most likely due to attempting to compare cell values that cannot be compared, eg one of the cells in one of the input ranges itself is an error. Just for testing include you could include something like this (though obviously don't leave the Stop/Resume in)
' first line of code (under the variable declarations) On Error GoTo errH ' code CountUnique = TheCount Exit Function errH: Stop ' press F8 to step to the error Resume End Function
CountUnique seems to mean the opposite of "to see what recurrence there is in multiple data sets", so I'm not sure what the objective is. If your two ranges are A1:A5 and B1:B5 and the 2x5 cells all include the same value, your function will return 25.
Peter Thornton