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