Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Function won't fire
Can anyone tell me why the following function to test the uniqueness o values in a (single and same) range fails to fire? It's based on th Worksheet Array formula *=MAX(COUNTIF(rng,rng)) *returning 1 fo "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 Myle -- Myle ----------------------------------------------------------------------- Myles's Profile: http://www.excelforum.com/member.php...fo&userid=2874 View this thread: http://www.excelforum.com/showthread.php?threadid=52299 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Function won't fire
I can see several problems:
There is no connection between the rng1 and rng2 input parameters and the string rng1,rng2 in evaluate which will be looking for excel defined named ranges called rng1 and rng2 if you solve this problem you wont need the Application.volatile Application.evaluate always assumes that that unqualified range references refer to the active sheet, so your function would not work unless both rng1 and rng2 happen to be sitting on the active sheet. You should add an on error handler to trap Evalute errors see http://www.decisionmodels.com/calcsecretsh.htm for discussion of how to use evaluate and an example UDF regards Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "Myles" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Function won't fire
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Function won't fire
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Function won't fire
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to set out a fire risk documunt | Excel Discussion (Misc queries) | |||
Code will not fire | Excel Programming | |||
Event doesn't fire | Excel Discussion (Misc queries) | |||
Custom function does not fire | Excel Programming | |||
Workbook_SheetChange will not fire | Excel Programming |