Hi all,

I need to loop through 2 ranges (same size, eg A2:A200, B2:B200)
a row at a time, check if the value in the second row is
equal to a specific cell and, if it is, add the value of the
first row to a collection.

I wrote this udf that works fine... but is SLOW!
How could I speed it up?

----------------------------
Public Function foo(rng1 As Range, rng2 As Range, cell) As Long
Dim x As Range
Dim i As Long
Dim unique_cod As New Collection

On Error Resume Next
i = 1
For Each x In rng1
If cell = rng2.Cells(i, 1) Then
'Debug.Print x; " <- "; x.Text; Cstr(x)
unique_cod.Add x, CStr(x)
End If
i = i + 1
Next x

'collection dump
'For i = 1 To unique_cod.Count
' Debug.Print unique_cod(i)
'Next i

foo = unique_cod.Count
End Function
-----------------------------

Thank you in advance, this ng is great!

Best regards,
Francesco

I guess I'm missing something but wouldn't the COUNTIF function do this?

--
Don Guillett
SalesAid Software

"fra" wrote in message news:opr93bu3raexgj8n@obi-i-one...

Hi all,

I need to loop through 2 ranges (same size, eg A2:A200, B2:B200)
a row at a time, check if the value in the second row is
equal to a specific cell and, if it is, add the value of the
first row to a collection.

I wrote this udf that works fine... but is SLOW!
How could I speed it up?

----------------------------
Public Function foo(rng1 As Range, rng2 As Range, cell) As Long
Dim x As Range
Dim i As Long
Dim unique_cod As New Collection

On Error Resume Next
i = 1
For Each x In rng1
If cell = rng2.Cells(i, 1) Then
'Debug.Print x; " <- "; x.Text; Cstr(x)
unique_cod.Add x, CStr(x)
End If
i = i + 1
Next x

'collection dump
'For i = 1 To unique_cod.Count
' Debug.Print unique_cod(i)
'Next i

foo = unique_cod.Count
End Function
-----------------------------

Thank you in advance, this ng is great!

Best regards,
Francesco

On Thu, 24 Jun 2004 07:52:18 -0500, Don Guillett wrote:

I guess I'm missing something but wouldn't the COUNTIF function do this?

A B
123 AA
222 BB
123 AA
444 CC
123 FF


How many *different* code I get in column A wich have "AA" in column B? one

I think COUNTIF() would give two.

Thank you
Fra

maybe this ARRAY formula to be entered with control+shift+enter instead of
just enter

=COUNT(IF(($B$1:$B$5=B1)*($A$1:$A$5),1))
--
Don Guillett
SalesAid Software

"fra" wrote in message news:opr93sfiq4exgj8n@obi-i-one...
On Thu, 24 Jun 2004 07:52:18 -0500, Don Guillett wrote:

I guess I'm missing something but wouldn't the COUNTIF function do this?

A B
123 AA
222 BB
123 AA
444 CC
123 FF


How many *different* code I get in column A wich have "AA" in column B?
one

I think COUNTIF() would give two.

Thank you
Fra

On Thu, 24 Jun 2004 13:40:02 -0500, Don Guillett wrote:

maybe this ARRAY formula to be entered with control+shift+enter instead
of
just enter

=COUNT(IF(($B$1:$B$5=B1)*($A$1:$A$5),1))

no, it gives 2 as result for AA.

I think I get the point: passing data from worksheets to VBA and vice
versa is
a s-l-o-w process.

here the explanation:
http://www.avdf.com/apr98/art_ot003.html

thank you anyway
Fra

I think you will get a 25%-50% improvement if you just change

rng2.Cells(i,1) to rng2(i,1). The .Cells, though a popular syntax for
some reason I have difficulty understanding, is relatively inefficient.

But I think you will get a significantly larger boost if you transfer
the ranges to VBA arrays and loop through the arrays instead of looping
through worksheet ranges, a la

Public Function foo1000(rng1 As Range, rng2 As Range, cell) As Long

Dim y() As Variant, z() As Variant
Dim i As Long
Dim unique_cod As New Collection
y = rng1
z = rng2

On Error Resume Next
i = 1
For Each Elem In y
If cell = z(i, 1) Then
'Debug.Print x; " <- "; x.Text; Cstr(x)
unique_cod.Add Elem, CStr(x)
End If
i = i + 1
Next Elem

'collection dump
'For i = 1 To unique_cod.Count
' Debug.Print unique_cod(i)
'Next i

foo1000 = unique_cod.Count
End Function

Please post back to let us know.

Alan Beban

fra wrote:

I think I get the point: passing data from worksheets to VBA and vice
versa is
a s-l-o-w process.

here the explanation:
http://www.avdf.com/apr98/art_ot003.html

thank you anyway
Fra

I guess the testing should be done before the posting. The line below
should be

unique_cod.Add Elem, CStr(Elem) rather than unique_cod.Add Elem, CStr(x)

Sorry,
Alan Beban

Alan Beban wrote:
I think you will get a 25%-50% improvement if you just change

rng2.Cells(i,1) to rng2(i,1). The .Cells, though a popular syntax for
some reason I have difficulty understanding, is relatively inefficient.

But I think you will get a significantly larger boost if you transfer
the ranges to VBA arrays and loop through the arrays instead of looping
through worksheet ranges, a la

Public Function foo1000(rng1 As Range, rng2 As Range, cell) As Long

Dim y() As Variant, z() As Variant
Dim i As Long
Dim unique_cod As New Collection
y = rng1
z = rng2

On Error Resume Next
i = 1
For Each Elem In y
If cell = z(i, 1) Then
'Debug.Print x; " <- "; x.Text; Cstr(x)
unique_cod.Add Elem, CStr(x)
End If
i = i + 1
Next Elem

'collection dump
'For i = 1 To unique_cod.Count
' Debug.Print unique_cod(i)
'Next i

foo1000 = unique_cod.Count
End Function

Please post back to let us know.

Alan Beban

fra wrote:


I think I get the point: passing data from worksheets to VBA and vice
versa is
a s-l-o-w process.

here the explanation:
http://www.avdf.com/apr98/art_ot003.html

thank you anyway
Fra

On Sat, 26 Jun 2004 00:07:45 -0700, Alan Beban wrote:

I guess the testing should be done before the posting. The line below
should be

unique_cod.Add Elem, CStr(Elem) rather than unique_cod.Add Elem, CStr(x)

Sorry,

no problem... and YES it is a boost! the calc time needed was a
"coffee+smoking break" and now is a "check this ng break" :-)

I think this info should be highlight somehow in the manual.

Alan Beban

thank you very much

Ciao
FF

De nada; thanks for the feedback.

Alan Beban

fra wrote:

On Sat, 26 Jun 2004 00:07:45 -0700, Alan Beban wrote:

I guess the testing should be done before the posting. The line below
should be

unique_cod.Add Elem, CStr(Elem) rather than unique_cod.Add Elem, CStr(x)

Sorry,


no problem... and YES it is a boost! the calc time needed was a
"coffee+smoking break" and now is a "check this ng break" :-)

I think this info should be highlight somehow in the manual.

Alan Beban


thank you very much

Ciao
FF