|
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 |
looping through ranges: how to speed up?
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 |
looping through ranges: how to speed up?
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 |
looping through ranges: how to speed up?
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 |
looping through ranges: how to speed up?
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 |
looping through ranges: how to speed up?
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 |
looping through ranges: how to speed up?
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 |
looping through ranges: how to speed up?
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 |
| All times are GMT +1. The time now is 03:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com