Hello Experts,

I am trying to look for a UDF solution.

My worksheet setup: Range A1:J30000 is my randomly generated number
sequence (numbers 1-99). And L1:U5000 is my database of archived
sequences.

I have simplified the following worksheet setup so it's easier to
explain and hopefully easier to construct code for:

A B C D E F G H I J K_______L M N O P Q R S T U _______V
1 2 3 4 5 6 7 8 9 10 bad____1 2 3 4 5 6 30 31 32 33____TRUE
____________________________1 2 3 4 5 6 30 31 32 33____TRUE
____________________________1 2 3 4 5 6 7 31 32 33_____FALSE
____________________________1 2 3 4 5 6 30 31 32 33____TRUE


If I am testing the similarity of the randomly generated sequence A1:J1
to 4 archive sequences L1:U1 L2:U2 L3:U3 & L4:U4. I use the array
formulas.

Cell K1 =IF(OR(FALSE=V1:V4),"bad","useful")

Cell V1 =SUM(COUNTIF(L1:U1,$A$1:$J$1))<7
Cell V2 =SUM(COUNTIF(L2:U2,$A$1:$J$1))<7
Cell V3 =SUM(COUNTIF(L3:U3,$A$1:$J$1))<7
Cell V4 =SUM(COUNTIF(L4:U4,$A$1:$J$1))<7


I am trying to avoid the 2 step formula process. I would like the UDF
to automatically test each row in the range L1:U4 and return a
conclusion of it's findings. In others words to set the above formulas
up as one user-defined function in VBA and simply pass along the
necessary variables for VBA to handle.

Based on the above worksheet example, all I would need to enter is the
UDF in Cell K1

=UDFName(L1:U4,A1:J1,"<7") returns the result "bad"

PLEASE NOTE: 1) The UDF must be able to test other range dimensions.
2)The test criteria must be variable, hence I can enter it in the
function.


I have a large amounts of data on my worksheet, and I need to check
30,000 combinations and additional databases. Can anybody help me to
find a UDF solution? Any help you could give me would be gratefully be
appreciated.


Regards

James