Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through ranges: how to speed up?
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOPING multiple ranges | Excel Discussion (Misc queries) | |||
Looping in VB with cell ranges | Excel Worksheet Functions | |||
Looping through named ranges | Excel Programming | |||
Looping through Ranges of Rows | Excel Programming | |||
Looping through Ranges of Rows | Excel Programming |