UDF ...VBA Formula built ...please help

142 de afișări
Accesați primul mesaj necitit

ytayta555

necitită,
6 aug. 2008, 18:05:3606.08.2008
A good day to you all

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 .

Joel

necitită,
6 aug. 2008, 21:58:0106.08.2008
Try calling function below with
=CountUDF(R1:BB15)


Function CountUDF(Target As Range)
CountUDF = True
For RowCount = 1 To Target.Rows.Count
MyCount = WorksheetFunction.Count(Target.Rows(RowCount))
If MyCount > 1 Then
CountUDF = False
'Exit Function
End If
Next RowCount
End Function

ytayta555

necitită,
6 aug. 2008, 22:16:0306.08.2008
On 7 Aug, 04:58, Joel <J...@discussions.microsoft.com> wrote:
> Try calling function below with

Thanks so much for help , but please take a look here :
http://newtonexcelbach.wordpress.com/2008/05/24/worksheetfunction-vs-udf-2/

How I said , I look for the maxim speed ! Thanks again

Joel

necitită,
6 aug. 2008, 22:31:0106.08.2008
Here is another way of doing it without worksheetfunction. I would compare
the two results

Function CountUDF(Target As Range)
CountUDF = True
For RowCount = 1 To Target.Rows.Count

MyCount = 0
ForColcount = 1 to Target.Columns.Count
if IsNumeric(Target.Cells(RowCount,ColCount)) then
MyCount = MyCount + 1


If MyCount > 1 Then
CountUDF = False
'Exit Function

end if
End If
Next ColCount
Next RowCount
End Function

Not sure which on the average will work faster.

ytayta555

necitită,
6 aug. 2008, 22:47:1106.08.2008
Please very much to provide me and VBA Functions and for BD93 ,
BD93 , BD121
and BD122 , to understand how I must write the others (from BD91 to
BD65536 )

Joel

necitită,
6 aug. 2008, 22:57:0106.08.2008
You don't change the function, just change the call

=CountUDF(R1:BB15)

ytayta555

necitită,
7 aug. 2008, 04:54:5707.08.2008
On 7 Aug, 05:57, Joel <J...@discussions.microsoft.com> wrote:
> You don't change the function, just change the call
> =CountUDF(R1:BB15)

I understand that in BD92 I must call =CountUDF(R1:BB14;R16:BB16),
in BD93 =CountUDF(R1:BB14;R17:BB17)
BD121 =CountUDF(R1:BB14;R45:BB45)
BD122 =CountUDF(R1:BB13;R15:BB16)
BD123 =CountUDF(R1:BB13;R15:BB15;R17:BB17)

Am I right ??
But , how I said I want to built them
without use worksheetfunction ;maybe , if it is posible ,
using array ...excuse me I ask more things , but I need them .
Thanks a lot for your time

Joel

necitită,
7 aug. 2008, 06:45:0107.08.2008
I have found that worksheet functions are usually very effient unless there
is a less complicated method using a UDF. If you have a UDF it is more
efficient to use macro code rather than call a worksheet function when there
ae equivalent methods. It also doesn't use much overhead to call a worksheet
function.

You could use and auxilary column BC and add this formula to row 1 and copy
down the worksheet.
=Count(R1:BB1)<=1

Then at the bottom of the row use Countif to determine if you have any non 1
values
=and(BC1:BC17)

ytayta555

necitită,
7 aug. 2008, 08:11:5107.08.2008
On 7 Aug, 13:45, Joel <J...@discussions.microsoft.com> wrote:
> I have found that worksheet functions are usually very effient unless there
> is a less complicated method using a UDF.  If you have a UDF it is more
> efficient to use macro code rather than call a worksheet function when there
> ae equivalent methods.  It also doesn't use much overhead to call a worksheet
> function.


Indeed , UDF have problem with recalculation , the problem of volatile
formula ,
can occure wrong results ,....; I'll try to see the best method , but
in time ;
maybe you can show me how is to use a macro ...
( If you have a UDF it is more


> efficient to use macro code rather than call a worksheet function when there
> ae equivalent methods. It also doesn't use much overhead to call a worksheet

> function. )
... to understand what is in this ideea .

Thanks so much you initiate me in this new method for me !!

Maybe , another last method and the best is to use , according with
this .....[ Another thing to keep in mind is that a UDF will more
often than
not be less efficient than a deeply nested group of standard
Functions.
This is because Excel's built in Functions are written in the
extremely
fast language, C++. VBA, unfortunately, is a very slow programming
language.]
...according with this , to use C++ for my database .Now , my database
is
in Excel application and VBA programming language , but it isn't
bad ! ...

Many thanks again mr. Joel , any ideea and sugestion here is very
apreciated
and usefull for me

Joel

necitită,
7 aug. 2008, 09:11:0107.08.2008
UDF only have calculation issues if you are referencing worksheet cells
inside a UDF that are not passed as a parameter. the fix is easy by passing
all the worksheet cells through the calling parameters.

ytayta555

necitită,
10 aug. 2008, 15:46:2110.08.2008
HI EVERYBODY AGAIN !

Last days I worked with this UDF .This UDF with worksheetfunction
don't
work : Try calling function below with
=CountUDF(R1:BB15)

Function CountUDF(Target As Range)
CountUDF = True
For RowCount = 1 To Target.Rows.Count
MyCount = WorksheetFunction.Count(Target.Rows(RowCount))
If MyCount > 1 Then
CountUDF = False
'Exit Function
End If
Next RowCount
End Function

It work perfect in the next variant :

Function CountUDF(Target As Range)
Application.Volatile
CountUDF = True
For ColCount = 1 To Target.Columns.Count
MyCount = WorksheetFunction.Count(Target.Columns(ColCount))


If MyCount > 1 Then
CountUDF = False
'Exit Function
End If

Next ColCount
End Function (I changed Row with Column and work perfect)

With my array function , an autofill from BD91 to BD65536 take 65
seconds ;
with this UDF with worksheetfunction inside , take 27 seconds (it's
great!)

The example of UDF without worksheetfunction inside , don't work !
Please
very much everybody to provide me the changes in it to get it work ,
because
work more more fast ( see
http://newtonexcelbach.wordpress.com/2008/05/24/worksheetfunction-vs-...
)

(In a perfect world I'd want so much to be with an array , according
with
the ideas and experiment of here :( see please

Please everybody to help me , I'm so nearby to get what I need and
want so much !
Thanks very much to all

Joel

necitită,
10 aug. 2008, 20:28:0110.08.2008
First, you should put back the "Exit function" to speed up the code. Once
you find one row that is greater than 1 you don't need to test all the other
rows.

Second, it doesn't make sence that the columns work and the Rows don't work.
It must mean that you have two items in one Row but not two items in one
column.

Third, there is an equivalent Count function in VBA to the worksheet
function. It will run a little quicker, but probably not noticable.

Function CountUDF(Target As Range)
CountUDF = True
For RowCount = 1 To Target.Rows.Count

MyCount = Target.Rows(RowCount).Count


If MyCount > 1 Then
CountUDF = False
Exit Function
End If
Next RowCount
End Function

ytayta555

necitită,
11 aug. 2008, 11:18:2411.08.2008
On 11 Aug, 03:28, Joel <J...@discussions.microsoft.com> wrote:
> First, you should put back the "Exit function" to speed up the code.  Once
> you find one row that is greater than 1 you don't need to test all the other
> rows.

Thanks so much for quick reply mr. Joel !
It's amasing : now , for an autofill from BD91
to BD65536 take 6 seconds ! It's the dream that I was looking
for !...Here can see
everybody the power of an UDF ...

> Second, it doesn't make sence that the columns work and the Rows don't work.
>  It must mean that you have two items in one Row but not two items in one
> column.

Indeed , in one row I can have don't matter how many items , but if in
a column
is more then 1 item , that't what I must find .(My apologise that I
wasn't able to explain
clear , but that's why I posted my worksheet formulas
AND(COUNT(....) , and
AND(MMULT(TR...)) )

> Third, there is an equivalent Count function in VBA to the worksheet
> function.  It will run a little quicker, but probably not noticable.
>
> Function CountUDF(Target As Range)
> CountUDF = True
> For RowCount = 1 To Target.Rows.Count
>    MyCount = Target.Rows(RowCount).Count
>    If MyCount > 1 Then
>       CountUDF = False
>       Exit Function
>    End If
> Next RowCount
> End Function

VBA still don't work , but I'm very pleasent for the actual result , 6
seconds
One only problem is now .I have milions of function with references in
combinatoric
order , which , for example, can look so :

=CountUDF(R37:BB37;R39:BB39;R41:BB41;R43:BB43;R45:BB45;R47:BB47;R49:BB49;R51:BB51
;R53:BB53;R55:BB55;R57:BB57;R59:BB59;R61:BB61;R66:BB66;R83:BB83)
with this kind of target UDF function don't work ; it's enough to
have , for example ,
=CountUDF(R37:BB51;R55:BB55) , and it don't work .

Is this an multiple target ? ...is this an array ? ......I don't
know ...
It's last my problem I must fix , the last improvement for speed with
VBA without function inside
doesn't matter so much .
Exist it a way to fix and my last problem ?

[maybe some sugestions for my last problem are here ,
http://www.dailydoseofexcel.com/archives/2007/09/18/performance-monitor/
in the 3-th Doug Jenkins comment ??..]

ytayta555

necitită,
11 aug. 2008, 17:07:2011.08.2008
Any kind of sugestion here is more then wellcome for me . Many thanks

Joel

necitită,
12 aug. 2008, 01:06:0412.08.2008
I think you can use a parameter array. when call the array seperate the
calling variable with a comma instead of a semicomma


Function CountUDF(ParamArray Target() As Variant)
CountUDF = True
MyCount = 0
For MyRow = 0 To UBound(Target())

For RowCount = 1 To Target(MyRow).Rows.Count
MyCount = MyCount + _
WorksheetFunction.Count(Target(1).Rows(RowCount))


If MyCount > 1 Then
CountUDF = False
Exit Function
End If
Next RowCount

Next MyRow
End Function

ytayta555

necitită,
12 aug. 2008, 02:32:3712.08.2008
On 12 Aug, 08:06, Joel <J...@discussions.microsoft.com> wrote:
> I think you can use a parameter array.  when call the array seperate the
> calling variable with a comma instead of a semicomma

IT WORK PERFECT !

5 seconds for an BD91:BD65536 autofill , I dont think
here can be another improvements ;

YOU PROVIDED ME THE DREAM
THAT I WAS LOOKING FOR TO GET IT , no words to thank
you enough for your patience and knowledge share .

Mr. Joel , what can I say ?
Only thing that I wonder is if I shall can sometimes in my life
to can do what you just done for me ! ... Big question for me ;
I'd like to know that for one day the answer will be positive

ytayta555

necitită,
17 aug. 2008, 23:42:2517.08.2008
A good day to everybody

I'm so sorry , but I was just hurry , and I haven't check this
UDF function enough ; it is still not working !!...

Is very easy to check : let's work in range B1:G20 ;
we shall use for this example the next macro :


Function CountUDF(ParamArray Target() As Variant)
CountUDF = True
MyCount = 0
For MyRow = 0 To UBound(Target())
For RowCount = 1 To Target(MyRow).Rows.Count
MyCount = MyCount + _
WorksheetFunction.Count(Target(1).Rows(RowCount))
If MyCount > 1 Then
CountUDF = False
Exit Function
End If
Next RowCount
Next MyRow
End Function

in column I row 8 we insert
the UDF =CountUDF(B1:G10;B14:G16;B18:G20) . Without any value in all
cells in
this range (B1:G10) , the UDF return FALSE , what is totaly wrong ,
because this UDF
I need to loop in every column of *ALL* arrays - in all arrays such
as it is one only
column in a only array ;
+++++++++++++++++++++++++++++++++++++++++
in next variant it begin to work , but need some little more
changes :

Function CountUDF(ParamArray Target() As Variant)
CountUDF = True
MyCount = 0
For MyRow = 0 To UBound(Target())

For ColCount = 1 To Target(MyRow).Columns.Count


MyCount = MyCount + _

WorksheetFunction.Count(Target(1).Columns(ColCount))


If MyCount > 1 Then
CountUDF = False
Exit Function
End If

Next ColCount
Next MyRow
End Function

with this line of code :
WorksheetFunction.Count(Target(1).Columns(ColCount)) ,
if we insert in Range("B5:G5") Value = 1 (for example) , the UDF
=CountUDF(B1:G10;B14:G16;B18:G20) will return TRUE , what is right ;
then we
insert in column B14 Value = 5 (for example, doesn't matter if value
is big or small)
, the UDF will return FALSE , what is right ;
BUT , if we delete the content of B14 , and put the value in B19 ,
the UDF will return
TRUE , what is wrong ;
It mean code don't recognise target
2 .....ction.Count(Target(1).Columns(ColCount))

++++++++++++++++++++++++++++++++++++++++
If we have the line of code :
WorksheetFunction.Count(Target(2).Columns(ColCount)),
(Target(2) instead of (Target(1) instead , if we insert in column
B14 Value = 5
, the UDF will return TRUE , what is wrong ;
BUT , if we delete the content of B14 , and put the value in B19 ,
the UDF
will return FALSE , what is wright ;
It mean code don't recognise target 1 in this case ;
What I need is the declaration of this targets , to recognise all
targets I need .

If I have 10 targets , it's enough for me ; to recognise something
like :
WorksheetFunction.Count(Target(1;2;3;4;5;6;7;8;9;10).Columns(ColCount))

Thanks very much for your incredible help

ytayta555

necitită,
18 aug. 2008, 09:02:3318.08.2008
Please very much to provide me here any kind of ideas , I have
need very strong this UDF to begin built my database .

ytayta555

necitită,
18 aug. 2008, 11:34:4618.08.2008
If I may explain myself a little more what
I need to perform this UDF , is to count each
Column separately in a Range of Columns , and in
a Array of Rows . Thank you very much

ytayta555

necitită,
19 aug. 2008, 06:37:4619.08.2008
I have found myself here a kind of solution , which resolve only
one problem , the problem of recognise of (Target(1) and
(Target(2) :

Function CountUDF(ParamArray Target() As Variant)
CountUDF = True
MyCount = 0

For MyRow = 0 To UBound(Target())
For ColCount = 1 To Target(MyRow).Columns.Count
MyCount = MyCount + _

WorksheetFunction.Count(Target(1).Columns(ColCount)) + _
WorksheetFunction.Count(Target(2).Columns(ColCount))


If MyCount > 1 Then
CountUDF = False
Exit Function
End If
Next ColCount
Next MyRow
End Function

But still is don't resolved the problem of counting separately
each column , to find in every separately column from array
if it have more that 1 item (number) in it .

Oh , programmers , I'm just crazy here , please to advice me .

ytayta555

necitită,
19 aug. 2008, 13:20:5319.08.2008
I believe that the answer for me is somewhere
around here :

http://www.cpearson.com/Excel/PassingAndReturningArrays.htm

http://www.cpearson.com/Excel/VBAArrays.htm#NumberOfDimensions

but I am totally lost here . Only if a profesional person work
with
dates of this pages , I can get what I need so strong . If I am not
impolite , exist another groups or forums where I can post this
thread ? I ask , because I don't know another better then this .

dou...@gmail.com

necitită,
20 aug. 2008, 06:55:5920.08.2008
ytayta555 - you left a comment on my blog, NewtonExcelBach (http://
newtonexcelbach.wordpress.com/), I'll reply here.

A few small changes may do what you want

First I'm assuming that ; is the correct array separator for your
region. For here (Australia) it's a comma.

Second it might be better to rename the variable "myrow" to "myrange",
since the array Target contains a number of ranges.

Then change: For myrow = 0 To UBound(Target())
to: For myrange = 0 To UBound(Target())

and change: WorksheetFunction.Count(Target(1).Rows(RowCount))
to: WorksheetFunction.Count(Target(myrange).Rows(RowCount))

and change: Next myrow to Next myrange

This will then:
loop through each range
in each range loop through each row
for each row count the number of cells that are not empty
When it finds more than one non-empty cell it will exit indicating
false
If there are 0 or 1 non-empty cells it will exit indicating true

Is that what you want?

For this application it may well be quicker to use
worksheetfunction.count than to try and do it entirely in VBA. To do
it in VBA you could:

Convert each range to a variant array:

range_array = Target(myrange).value2

Then loop through each row and each column of the array, and count non-
empty cells. But it may not be any quicker.

Finally with the code as it is, I don't see any purpose in looping
through each row. Why not simply do the count on each range?

ytayta555

necitită,
20 aug. 2008, 10:49:2220.08.2008
On 20 Aug, 13:55, doug...@gmail.com wrote:
> ytayta555 - you left a comment on my blog, NewtonExcelBach (http://
> newtonexcelbach.wordpress.com/), I'll reply here.
> A few small changes may do what you want

I didn't expect to get the answer from you youself sir Doug
Jenkins , I am very pleasantly surprised

> First I'm assuming that ; is the correct array separator for your
> region.  For here (Australia) it's a comma.

I am not wrong here , in european sintax array separator
is ; and for array formula is |

> Second it might be better to rename the variable "myrow" to "myrange",
> since the array Target contains a number of ranges.
> Then change: For myrow = 0 To UBound(Target())
> to:  For myrange = 0 To UBound(Target())
> and change:   WorksheetFunction.Count(Target(1).Rows(RowCount))
> to: WorksheetFunction.Count(Target(myrange).Rows(RowCount))
> and change: Next myrow to Next myrange

I made this changes , and it is a big improvement ! Now , UDF
recognise all targets .

In some few minutes we can work in range B1:G20 ,
to see and be shure on results ;


we shall use for this example the next macro :
Function CountUDF(ParamArray Target() As Variant)
CountUDF = True
MyCount = 0

For myranges = 0 To UBound(Target())
For ColCount = 1 To Target(myranges).Columns.Count


MyCount = MyCount + _

WorksheetFunction.Count(Target(myranges).Columns(ColCount))


If MyCount > 1 Then
CountUDF = False
Exit Function
End If
Next ColCount

Next myranges
End Function

In cell I8 we put the UDF : =CountUDF(B1:G10;B14:G16;B18:G20)
If we put a number in cell B1 and then in B7 , will return False =
good result !
The same thing if we put the number in B15 or B 19 instead of B7 .
BUT , if we have a number in cell B1 and we put a number in cell C19 ,
or
C15 , the UDF will return False , what is wrong .

If we have in all range we work (B1:G20) in cell B1 a number and in
cell D19 another number, the right
result for me is TRUE , because there are not 2 numbers in column
B !!
Actually , UDF don't recognise to count every column separately (to
count the same column in this
three ranges like is a one range ( to recognise column
B1:B10;B14:B16;B18:B20 like a *single* range ).
- - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
[ An example what I need : The worksheet formula look so in this
example : |
=AND(COUNT(B1:B10;B14:B16;B18:B20)<=1;COUNT(C1:C10;C14:C16;C18:C20)<=1;COUNT
|
(D1:D10;D14:D16;D18:D20)<=1;.....E...........F.............COUNT(G1:G10;G14:G16;G18:G20)<=1)
|
Here we can see that the worksheet formula count in every column
separate ] |
- - - - - - - - - - -- - - - - - - - - - - - -- -- - - - - - - - -
- - - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -

> This will then:
>  loop through each range

I need to loop through each column in each range , such as
it is a only column in range B1:B10;B14:B16;B18:B20
but , actually , it count like in three columns (ranges) separate !...
The problem is that this UDF actually count in 3 ranges , not
like in one range , each Column must be recognised like an
only range .

>  in each range loop through each row
>  for each row count the number of cells that are not empty

I need to loop and count in every column , not row

> Is that what you want?

My last problem to fix is what I just tried to explain

> For this application it may well be quicker to use
> worksheetfunction.count than to try and do it entirely in VBA.  To do
> it in VBA you could:
> Convert each range to a variant array:
> range_array = Target(myrange).value2
> Then loop through each row and each column of the array, and count non-
> empty cells.  But it may not be any quicker.

Here , I'm totally lose , I need like a beginner all code , but it
doesn't matter
so much , I can not to ask you more , I am very pleased to fix this my
last
problem ( to count in every column separate but not how they are 3
ranges
separate ).

> Finally with the code as it is, I don't see any purpose in looping
> through each row.  Why not simply do the count on each range?

I'll have not just one UDF function , I'll have millions of this kind
of UDF ,
that's why I am looking for fastest speed , an improvement of 1
second
per workbook is very much for me ; with array worksheet formula it
take
60 seconds per workbook , with this UDF can take and 10 seconds
or more slightly , because worksheet formula doesn't have :
,, If MyCount > 1 Then
CountUDF = False
Exit Function ,, ............
> Joel wrote ...First, you should put back the "Exit function" to speed up the code. Once


you find one row that is greater than 1 you don't need to test all the
other rows.

I tried in many ways , it is the best and fastest kind of querry in my
kind of database .


ytayta555

necitită,
20 aug. 2008, 18:39:2720.08.2008
On 20 Aug, 13:55, doug...@gmail.com wrote:

> A few small changes may do what you want

> Is that what you want?

I worked , and I have resolved the last but one problem .

With the next code the UDF recognise to count each column
separate in every range, but do not recognise to count in all
ranges of the array the same column as like it is one
range :

Function CountUDF(ParamArray Target() As Variant)
CountUDF = True

For myrange = 0 To UBound(Target())

For ColCount = 1 To Target(myrange).Columns.Count
MyCount =
WorksheetFunction.Count(Target(myrange).Columns(ColCount))


If MyCount > 1 Then
CountUDF = False
Exit Function
End If
Next ColCount

Next myrange
End Function

With the our previous code , UDF recognise to count in
all ranges of the array , but not in the each separate column
of every range as like it is one range :

Function CountUDF(ParamArray Target() As Variant)
CountUDF = True
MyCount = 0

For myrange = 0 To UBound(Target())

For ColCount = 1 To Target(myrange).Columns.Count


MyCount = MyCount + _

WorksheetFunction.Count(Target(myrange).Columns(ColCount))


If MyCount > 1 Then
CountUDF = False
Exit Function
End If
Next ColCount

Next myrange
End Function

dou...@gmail.com

necitită,
21 aug. 2008, 01:10:1421.08.2008
OK, I think I understand what you want better now.

You need to put the For to loop through the columns first, then loop
through each range, and you also need to reset MyCount to zero for
each column.

This should work:

Function CountUDF(ParamArray Target() As Variant)
CountUDF = True

For ColCount = 1 To Target(0).Columns.Count


MyCount = 0
For myranges = 0 To UBound(Target())

MyCount = MyCount +


WorksheetFunction.Count(Target(myranges).Columns(ColCount))
If MyCount > 1 Then
CountUDF = False
Exit Function
End If

Next myranges
Next ColCount
End Function

This will take the number of columns in the first range as being the
same as all the other ranges. You could add some code to check the
number of columns in each range, and give a warning if they are not
all the same.


On Aug 21, 12:49 am, ytayta555 <wherewindsm...@gmail.com> wrote:
> On 20Aug, 13:55, doug...@gmail.com wrote:
>
> >ytayta555- you left a comment on my blog, NewtonExcelBach (http://

> =AND(COUNT(B1:B10;B14:B16;B18:B20)<=1;COUNT(C1:C10;C14:C16;C18:C20)<=1;COUN­T

ytayta555

necitită,
21 aug. 2008, 19:01:1121.08.2008
On 21 Aug, 08:10, doug...@gmail.com wrote:
> OK, I think I understand what you want better now.
> This should work:

IT WORK PERFECT ! IT WORK !

Thank you very much , mr. Doug Jenkins , you're amasing !
Thank you both , and to Joel ; if you believe me or not ,
you made me a MAN , this UDF was my biggest problem .


Now , this UDF for an autofill BD91:BD65536 take
10 seconds , it's a dream .

>You could add some code to check the number of
>columns in each range, and give a warning if they are not
> all the same.

This is not a problem ; this can be maybe a last
improvement : for example 0,3 seconds speed improvement
per workbook for me is very much , it provide me a database
bigger with some few millions functions .
I tried to find the right code for VBA only , to can see the
diference between speed , but without any succes :


Function CountUDF(ParamArray Target() As Variant)
CountUDF = True

range_array = Target(myrange).Value2


For ColCount = 1 To Target(0).Columns.Count
MyCount = 0
For myranges = 0 To UBound(Target())

MyCount = Target(myranges).Columns(ColCount).Count

ytayta555

necitită,
22 aug. 2008, 03:46:3622.08.2008
I 'll can not to reply/comunicate here before
monday . A good week-end , gentlemans .

dou...@gmail.com

necitită,
23 aug. 2008, 08:25:1923.08.2008
Here is a version of the UDF that converts the ranges to arrays:

Function CountUDFA(ParamArray Target() As Variant) As Boolean
Dim NumRanges As Long, NumRows() As Long, NumCols As Long, ColCount As
Long
Dim MyCount As Long, MyRanges As Long, Target2 As Variant, RowNum As
Long

CountUDFA = True
NumRanges = UBound(Target()) - LBound(Target()) + 1
ReDim NumRows(0 To NumRanges - 1)

For MyRanges = LBound(Target()) To UBound(Target())
Target(MyRanges) = Target(MyRanges).Value2
NumRows(MyRanges) = UBound(Target(MyRanges)) -
LBound(Target(MyRanges)) + 1
Next MyRanges

NumCols = UBound(Target(0), 2) - LBound(Target(0), 2) + 1

For ColCount = 1 To NumCols
MyCount = 0
For MyRanges = LBound(Target()) To UBound(Target())
Target2 = Target(MyRanges)
For RowNum = 1 To NumRows(MyRanges)
If Not IsEmpty((Target2(RowNum, ColCount))) Then


MyCount = MyCount + 1
If MyCount > 1 Then

CountUDFA = False
Exit Function
End If
End If
Next RowNum
Next MyRanges
Next ColCount
End Function


With 3 very big ranges (in Excel 2007) it is much slower than the
previous version, but with a lot of small ranges it might be quicker.
I will leave it to you to check that.

ytayta555

necitită,
25 aug. 2008, 16:56:5925.08.2008
On 23 Aug, 15:25, doug...@gmail.com wrote:
> Here is a version of the UDF that converts the ranges to arrays:
> With 3 very big ranges (in Excel 2007) it is much slower than the
> previous version, but with a lot of small ranges it might be quicker.
> I will leave it to you to check that.

I'm very pleasantly surprised for this high level VBA knowledge
demonstration ,
used in interest of my help and needs .

My results are the next :
1) =CountUDF(R1:BB12;R15:BB17) = 9,31 seconds
=CountUDFA(R1:BB12;R15:BB17) = 16,15 seconds

2) =CountUDF(R1:BB8;R15:BB17;R20:BB24;R26:BB26) = 12,92 sec
The same Target UDFA = 17,67 sec

3)
=CountUDF(R1:S12;R15:S17;T1:U12;T15:U17;V1:W12;V15:W17;X1:Y12;X15:Y17;Z1:AA12;Z15:AA17;AB1:AC12;AB15:AC17;AD1:AE12;AD15:AD17;AE1:BB12;AE15:AE17)
= 7,22
UDFA = 14,65

4)
=CountUDFA(R1:BB2;R4:BB4;R6:BB6;R8:BB8;R10:BB10;R12:BB12;R14:BB14;R16:BB16;R18:BB18;R20:BB21;R23:BB25)
= 30,78
UDFA = 40,85

5)
=CountUDF(R1:S12;R15:S17;T1:U12;T15:U17;V1:W12;V15:W17;X1:Y12;X15:Y17;Z1:AA12;Z15:AA17;AB1:AC12;AB15:AC17;AD1:AE12;AD15:AE17;AF1:BB12;AF15:BB17)
= 6,65
UDFA = 14,63

6)
=CountUDF(R1:S6;R15:S17;T1:U6;T15:U17;V1:W6;V15:W17;X1:Y6;X15:Y17;Z1:AA6;Z15:AA17;AB1:AC6;AB15:AC17;AD1:AE6;AD15:AE17;AF1:BB6;AF15:BB17;R8:S13;T8:U13;V8:W13;X8:Y13;Z8:AA13;AB8:AC13;AD8:AE13;AF8:BB13)
= 10,72
UDFA = 19,00

7)
=CountUDF(R1:S12;R15:S17;T1:U12;T15:U17;V1:W12;V15:W17;X1:Y12;X15:Y17;Z1:AA12;Z15:AA17;AB1:AC12;AB15:AC17;AD1:AE12;AD15:AD17;AE1:AF12;AE15:AF17;AG1:AH12;AG15:AH17;AI1:AJ12;AI15:AJ17;AK1:AL12;AK15:AL17;AM1:AN12;AM15:AN17;AO1:BB12;AO15:BB17)
= 7,98
UDFA = 20,14

8)
=CountUDF(R1:BB1;R3:BB3;R5:BB5;R7:BB7;R9:BB9;R11:BB11;R13:BB13;R15:BB15;R17:BB17;R19:BB19;R21:BB21;R23:BB23;R15:BB15;R27:BB27;R29:BB29)
= 55,1
UDFA = 30,78

[I have to work in columns from R to BB , and to have in function 15
rows , doesn't matter
in how many ranges , but if them are divided in two ranges , like in
result 5 , the results
seems to be faster ]

For a first look , the results seems to be faster with worksheet
function inside ;
(can be maybe a combination of arrays working with worksheet
function , with
a better speed ? it was just only an idea , I'm totally lost for this
level .. )

THANKS SO MUCH FOR YOUR EFFORT , mr. DOUG JENKINS

Răspundeți tuturor
Răspundeți autorului
Redirecționați
0 mesaje noi