And just to add to Tom's comments.
I'd use .address(external:=true) in his expression (both spots).
Then I wouldn't have to worry about what sheet was active or where the ranges
were.
? application.Evaluate("Sum(countif(" & rng1.Address(external:=true) & "," & _
rng2.address(external:=true) & "))")
Tom Ogilvy wrote:
I don't think that will work if rng2 is a multicell range. It gives me a
type mismatch error.
this worked for me: (demo'd from the immediate window)
set rng1 = Range("A1:A10")
set rng2 = Range("B1:B10")
? application.Evaluate("Sum(countif(" & rng1.Address & "," & _
rng2.address & "))")
2
this also appears to work
? application.Sumproduct(application.Countif(rng1,rn g2))
4
although I would consider this a special case. Using worksheetfunction as a
qualifier of Countif in this instance causes it to fail, however.
--
Regards,
Tom Ogilvy
"Ardus Petus" wrote:
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
--
Dave Peterson