This should work:

Function UniqueTest(rng1 As Range, rng2 As Range) As String
Dim x As Long

Application.Volatile

x = Application.WorksheetFunction.CountIf(rng1, rng2)
If x = 1 Then
UniqueTest = "Unique"
Else
UniqueTest = "Not Unique"
End If

End Function

HTH
--
AP

"Myles" a écrit dans le
message de ...

Can anyone tell me why the following function to test the uniqueness of
values in a (single and same) range fails to fire? It's based on the
Worksheet Array formula *=MAX(COUNTIF(rng,rng)) *returning 1 for
"unique", otherwise "not unique".

Function UniqueTest(rng1 As Range, rng2 As Range) As String

Application.Volatile

x = Application.Evaluate("Max(Countif(rng1,rng2))")
If x = 1 Then
UniqueTest = "Unique"
Else
UniqueTest = "Not Unique"
End If

End Function


and neither does ...

Function UniqueTest2(rng1 As Range, rng2 As Range) As String

Application.Volatile

x = [Max(Countif(rng1,rng2))]
If x = 1 Then
UniqueTest2 = "Unique"
Else
UniqueTest2 = "Not Unique"
End If

End Function

TIA

Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=522993