Y

#### ytayta555

I forever was searching for maxim speed in my database ;

I never hear before by User Defined Function/Formula , UDF ;

Now , my extreme important goal is to built in column BD ,

Range from row 91 to 65536 my UDF , but I dont know nothing

about VBA Function/ Formula built ! ...

in Range("BD91") , to explain what my worksheet formula do, I show

you this formula :

=AND(COUNT(R1;R2;R3;R4;R5;R6;R7;R8;R9;R10;R11;R12;R13;R14;R15)<=1;COUNT(S1;S2;S3;S4;S5;S6;S7;S8;S9;S10;S11;S12;S13;S14;S15)<=1;COUNT(T1;T2;T3;T4;T5;T6;T7;T8;T9;T10;T11;T12;T13;T14;T15)<=1;COUNT(U1;U2;U3;U4;U5;U6;U7;U8;U9;U10;U11;U12;U13;U14;U15)<=1;COUNT(V1;V2;V3;V4;V5;V6;V7;V8;V9;V10;V11;V12;V13;V14;V15)<=1;COUNT(W1;W2;W3;W4;W5;W6;W7;W8;W9;W10;W11;W12;W13;W14;W15)<=1;COUNT(X1;X2;X3;X4;X5;X6;X7;X8;X9;X10;X11;X12;X13;X14;X15)<=1;COUNT(Y1;Y2;Y3;Y4;Y5;Y6;Y7;Y8;Y9;Y10;Y11;Y12;Y13;Y14;Y15)<=1;COUNT(Z1;Z2;Z3;Z4;Z5;Z6;Z7;Z8;Z9;Z10;Z11;Z12;Z13;Z14;Z15)<=1;COUNT(AA1;AA2;AA3;AA4;AA5;AA6;AA7;AA8;AA9;AA10;AA11;AA12;AA13;AA14;AA15)<=1;COUNT(AB1;AB2;AB3;AB4;AB5;AB6;AB7;AB8;AB9;AB10;AB11;AB12;AB13;AB14;AB15)<=1;COUNT(AC1;AC2;AC3;AC4;AC5;AC6;AC7;AC8;AC9;AC10;AC11;AC12;AC13;AC14;AC15)<=1;COUNT(AD1;AD2;AD3;AD4;AD5;AD6;AD7;AD8;AD9;AD10;AD11;AD12;AD13;AD14;AD15)<=1;COUNT(AE1;AE2;AE3;AE4;AE5;AE6;AE7;AE8;AE9;AE10;AE11;AE12;AE13;AE14;AE15)<=1;COUNT(AF1;AF2;AF3;AF4;AF5;AF6;AF7;AF8;AF9;AF10;AF11;AF12;AF13;AF14;AF15)<=1;COUNT(AG1;AG2;AG3;AG4;AG5;AG6;AG7;AG8;AG9;AG10;AG11;AG12;AG13;AG14;AG15)<=1;COUNT(AH1;AH2;AH3;AH4;AH5;AH6;AH7;AH8;AH9;AH10;AH11;AH12;AH13;AH14;AH15)<=1;COUNT(AI1;AI2;AI3;AI4;AI5;AI6;AI7;AI8;AI9;AI10;AI11;AI12;AI13;AI14;AI15)<=1;COUNT(AJ1;AJ2;AJ3;AJ4;AJ5;AJ6;AJ7;AJ8;AJ9;AJ10;AJ11;AJ12;AJ13;AJ14;AJ15)<=1;COUNT(AK1;AK2;AK3;AK4;AK5;AK6;AK7;AK8;AK9;AK10;AK11;AK12;AK13;AK14;AK15)<=1;COUNT(AL1;AL2;AL3;AL4;AL5;AL6;AL7;AL8;AL9;AL10;AL11;AL12;AL13;AL14;AL15)<=1;COUNT(AM1;AM2;AM3;AM4;AM5;AM6;AM7;AM8;AM9;AM10;AM11;AM12;AM13;AM14;AM15)<=1;COUNT(AN1;AN2;AN3;AN4;AN5;AN6;AN7;AN8;AN9;AN10;AN11;AN12;AN13;AN14;AN15)<=1;COUNT(AO1;AO2;AO3;AO4;AO5;AO6;AO7;AO8;AO9;AO10;AO11;AO12;AO13;AO14;AO15)<=1;COUNT(AP1;AP2;AP3;AP4;AP5;AP6;AP7;AP8;AP9;AP10;AP11;AP12;AP13;AP14;AP15)<=1;COUNT(AQ1;AQ2;AQ3;AQ4;AQ5;AQ6;AQ7;AQ8;AQ9;AQ10;AQ11;AQ12;AQ13;AQ14;AQ15)<=1;COUNT(AR1;AR2;AR3;AR4;AR5;AR6;AR7;AR8;AR9;AR10;AR11;AR12;AR13;AR14;AR15)<=1;COUNT(AS1;AS2;AS3;AS4;AS5;AS6;AS7;AS8;AS9;AS10;AS11;AS12;AS13;AS14;AS15)<=1;COUNT(AT1;AT2;AT3;AT4;AT5;AT6;AT7;AT8;AT9;AT10;AT11;AT12;AT13;AT14;AT15)<=1;COUNT(AU1;AU2;AU3;AU4;AU5;AU6;AU7;AU8;AU9;AU10;AU11;AU12;AU13;AU14;AU15)<=1;COUNT(AV1;AV2;AV3;AV4;AV5;AV6;AV7;AV8;AV9;AV10;AV11;AV12;AV13;AV14;AV15)<=1;COUNT(AW1;AW2;AW3;AW4;AW5;AW6;AW7;AW8;AW9;AW10;AW11;AW12;AW13;AW14;AW15)<=1;COUNT(AX1;AX2;AX3;AX4;AX5;AX6;AX7;AX8;AX9;AX10;AX11;AX12;AX13;AX14;AX15)<=1;COUNT(AY1;AY2;AY3;AY4;AY5;AY6;AY7;AY8;AY9;AY10;AY11;AY12;AY13;AY14;AY15)<=1;COUNT(AZ1;AZ2;AZ3;AZ4;AZ5;AZ6;AZ7;AZ8;AZ9;AZ10;AZ11;AZ12;AZ13;AZ14;AZ15)<=1;COUNT(BA1;BA2;BA3;BA4;BA5;BA6;BA7;BA8;BA9;BA10;BA11;BA12;BA13;BA14;BA15)<=1;COUNT(BB1;BB2;BB3;BB4;BB5;BB6;BB7;BB8;BB9;BB10;BB11;BB12;BB13;BB14;BB15)<=1)

An perfect equivalent for formula above is the next array formula

which I use actually :

=AND(MMULT(TRANSPOSE(ROW(R1:BB45))^0;ISNUMBER(R1:BB45)*{1|1|1|1|1|1|1|

1|1|1|1|1|1|1|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|

0|0|0})<=1)

in my locale formula sintax , and in US formula sintax :

=AND(MMULT(TRANSPOSE(ROW(R1:BB45))^0,ISNUMBER(R1:BB45)*{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0})<=1)

in Range("BD92") , all Count functions in formula shall have

COUNT(R1;R2;R3;R4;R5;R6;R7;R8;R9;R10;R11;R12;R13;R14;R16) [R16 not

R15 like in BD91!] ;my perfect equivalent array formula

shall look so :

=AND(MMULT(TRANSPOSE(ROW(R1:BB45))^0;ISNUMBER(R1:BB45)*{1|1|1|1|1|1|1|

1|1|1|1|1|1|1|0|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|

0|0|0})<=1)

in Range("BD93") , all Count functions in formula shall have

COUNT(R1;R2;R3;R4;R5;R6;R7;R8;R9;R10;R11;R12;R13;R14;R17) [R16 not

R15 like in BD91!] ;my perfect equivalent array formula

shall look so :

=AND(MMULT(TRANSPOSE(ROW(R1:BB45))^0;ISNUMBER(R1:BB45)*{1|1|1|1|1|1|1|

1|1|1|1|1|1|1|0|0|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|

0|0|0})<=1)

in Range("BD121") = When references from Count functions are

COUNT(R1;R2;R3;R4;R5;R6;R7;R8;R9;R10;R11;R12;R13;R14;R45) , then in

next cell of range(Range("BD122") references shall look so :

COUNT(R1;R2;R3;R4;R5;R6;R7;R8;R9;R10;R11;R12;R13;R15;R16) , so the

moral of this tale is that references are in combinatoric order , 45

numbers taken 15 times

Please very much to help me to built my array formula in VBA , this

UDF is very very important for me . If I have first 2 or 3 examples

for my array formula from BD91 and BD92 ,

I think I'll be able to understand how I must work for the next

others .I want to built in VBA this UDF , but with an array see

here please http://www.dailydoseofexcel.com/archives/2007/09/18/performance-monitor/

to Doug Jenkins comment ) .

[Maybe the array formula above is too complicate ; for an easyer

example , I believe if I have the below easyer example Formulas

write in VBA I'll be able to understand how I must work for the next

more complicate others :

=AND(COUNT(B1;B2;B3)<=1;COUNT(C1;C2;C3)<=1;COUNT(D1;D2;D3)<=1;COUNT(E1;E2;E3)<=1;COUNT(F1;F2;F3)<=1)

=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5)*{1|1|1|0|0})<=1)

=AND(COUNT(B1;B2;B3)<=1;COUNT(C1;C2;C4)<=1;COUNT(D1;D2;D4)<=1;COUNT(E1;E2;E4)<=1;COUNT(F1;F2;F4)<=1)

=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5)*{1|1|0|1|0})<=1)

=AND(COUNT(B1;B2;B3)<=1;COUNT(C1;C2;C5)<=1;COUNT(D1;D2;D5)<=1;COUNT(E1;E2;E5)<=1;COUNT(F1;F2;F5)<=1)

=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5)*{1|1|0|0|

1})<=1) ]

Thank you all so much .