Slow Program

[AD]

Hello,

I am loading a 14,000 line range into an array.

I am then looping through each element of that array, and using the find
method, am checking to see if that value is in a 40,000 line range in
another workbook.

This is running hopelessly slow, taking 30 minutes to execute.

Screenupdating and Calcuation are both off.

Any ideas would be greatly appreciated.

Thanks
Ariel

 

[Ed Hansberry, MS-MVP/Mobile Devices]

Hello,

I am loading a 14,000 line range into an array.

I am then looping through each element of that array, and using the find
method, am checking to see if that value is in a 40,000 line range in
another workbook.

This is running hopelessly slow, taking 30 minutes to execute.

Screenupdating and Calcuation are both off.

Any ideas would be greatly appreciated.

Do you have any Paste|Picture Link objects in the workbook? Those
are always set to automatic calculation regardless of what the
Calc settings are on.
--
__________________________________________________________________________________
Ed Hansberry (Please do *NOT* email me. Post here for the benefit of all)
What is on my Pocket PC? http://www.ehansberry.com/
Microsoft MVP - Mobile Devices www.pocketpc.com
What is an MVP? -

 

[Peter T]

Post a stripped down version of your code, enough to show the each part of
your methods.

Regards,
Peter T

 

[Jon Peltier]

Try loading the 40000 line range into a second array. Depending on how you
access the cells in the range, you can greatly change the speed of the
routine. Also, you might try a binary lookup on a sorted array.

- Jon

 

[AD]

Thanks,

I'll try your suggestions

Ariel

Try loading the 40000 line range into a second array. Depending on how you
access the cells in the range, you can greatly change the speed of the
routine. Also, you might try a binary lookup on a sorted array.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______