Michael,
Dunno about the relative speed of the two methods; but, if both lists are sorted, you can fairly easily write your own code for a find and update that should take a fraction of a second to execute. I've done it with updating a master list of 24K entries from a list of 19K and it's done "before my finger leaves the keyboard".
I also don't know if the two methods benefit from a sorted list.
Dave Gathmann
--- In ExcelVBA@yahoogroups.com, "mickey11030" <mickey11030@...> wrote:
>
> I have a worksheet with about 40,000 products, and a second worksheet with about 60,000 products. I have to match each of the 40,000 product IDs in the first sheet with the corresponding product IDs in the second to retrieve the current price. Not all the 40,000 products have matching IDs.
>
> I have written a VBA program based on the Find method that does this processing, but it runs for a couple of hours. Someone suggested the program would run faster if I used VLookup instead. I understand that using VLookup requires that the second sheet be sorted. I did a sort and was astonished that it was done almost instantaneously, so that would not be a concern.
>
> Before I do all the work involved in writing a new program based on VLookup, I wonder if anyone knows if that program is likely to run faster.
>
> Thanks,
> Michael Trombetta
>
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar