I use this one quite often, its handled thousands of values on both sheets
before, I have a feeling it can be improved by not activating the sheets to
look in them but its still pretty fast

Dim I, I2 As Integer
Dim S_Product, S_Desc As String


Sub Macro5()

I = 1

While Range("A" & I).Value < ""
S_Product = Range("A" & I).Value
Windows("wb2.xls").Activate
I2 = 1
While Range("A" & I2).Value < S_Product And Range("A" & I2).Value < ""
I2 = I2 + 1
Wend
If Range("A" & I2).Value < "" Then
S_Desc = Range("B" & I2).Value
Else
S_Desc = "not found"
End If
Windows("wb1.xls").Activate
Range("B" & I).Value = S_Desc
I = I + 1
Wend

End Sub


"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***