I'm pretty surprised that it is painfully slow to go through even a
few thousand rows to find a value ... a few questions on that:
1) Are you matching a cell value from WB1 to each cell value in WB2
e.g. "IF sheets("WB1").cells(i,j).value = sheets("WB2").activecell
then"? This could slow you down a bunch ... better to assign your
value from WB1 to a constant and then it's "IF varValue =
sheets("WB2").activecell then"....
2) Are you stopping the search for the value in WB2 once you've found
something? (Or are you going through all thousands of rows blindly?)
3) Are you tabbing back and forth from WB1 and WB2 a lot? This DOES
slow you down - you should try to minimize the use of .select if you
can (there's no problem with assigning a value to a cell in WB1 if
it's not open!
4) While running the macro did you set "Application.ScreenUpdating =
False" and then back to true when you're done?

If this doesn't help, you could try to assign a filter to WB2 and then
copy the values over to WB1 ....


That's all I got - post here again if it works or if it doesn't :-)

Chris

On Feb 28, 9:52*am, Jay wrote:
Hello all,

I'm having some performance issues with something I'm working on, and I'm
hoping someone can point out some solutions. *Maybe there's something I can
take advantage of that I didn't know about it.

I've got a list of product numbers. *I'm trying to match the product number
(WB1) to a description that is contained in another workbook (WB2). *WB1 and
WB2 can both contain thousands of rows.

This product number/description matching is being done in a macro that does
additional formatting. *The macro runs fine until I get to this function..

My first attempt was to set the cell formula in WB1 to a vlookup to get the
value from WB2. *This is PAINFULLY slow. *I tried turning calculation to
manual, but it hasn't helped.

Then I thought I would search for the product number in WB2 myself. *So I
have a function (in WB1) that opens WB2, and searches (in a do while loop)
through the appropriate column for a product number, and if found retrieves
the product description and places it in a cell in WB1. *This is also
PAINFULLY slow.

I'm at a complete loss. *I don't know any way to retrieve the value need
from WB2 without significant slowdown. *I need to keep modifications to WB2
to a minimum, but if I can expect improved performance, I will push for
modifications. *And I can't be sure that the list will be sorted or anything
like that.

If anyone has any insight into this issue, I would LOVE to hear it. *I'll
take anything you've got.

Thanks,
Jay
--
Disregard, this is so I can find my post later.
***postedbyJay***