Help with speeding up vlookup macro

I am working on a macro to compare two lists of job numbers in two
separate workbooks. If there are any matches, those duplicate jobs are
flagged. I used vlookup to accomplish this. My problem is that my
table_array could have as many as 10000 cells. When I use vlookup to
check 200 different lookup_values, the processing time is, to say the
least, massive.

Is there any way to speed up this process? I had thought of sorting the

table_array, since there are a lot of blank cells in it. I guess my
main question is, how would I have one workbook sort a column in
another workbook?


Any help would be appreciated.

[email protected] wrote:
> I am working on a macro to compare two lists of job numbers in two
> separate workbooks. If there are any matches, those duplicate jobs are
> flagged. I used vlookup to accomplish this. My problem is that my
> table_array could have as many as 10000 cells. When I use vlookup to
> check 200 different lookup_values, the processing time is, to say the
> least, massive.
>
> Is there any way to speed up this process? I had thought of sorting the
>
> table_array, since there are a lot of blank cells in it. I guess my
> main question is, how would I have one workbook sort a column in
> another workbook?
>
>
> Any help would be appreciated.

Please post your code so we can look at it and offer suggestions. One
thing i can say right now though is to investivge
application.screenupdating = false

AR

Sub ShippedWIP()
Columns("k:k").Select
Selection.Insert Shift:=xlToRight
Range("B26").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 9).Select
ActiveCell.FormulaR1C1 = "1"
Range("k26").Select
ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-9],'[crViewer.xls]Sheet1'!R1C2:R1000C2,0,FALSE)"
Range("k26").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Columns("k:k").EntireColumn.AutoFit
Range("k26").Select
Do Until IsEmpty(ActiveCell) = True
If ActiveCell = CVErr(xlErrValue) Then
ActiveCell.Offset(0, -3).Select
Range(Selection, Selection.End(xlToLeft)).Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
ActiveCell.Offset(1, 10).Select
Else: ActiveCell.Offset(1, 0).Select
End If
Loop
Columns("k:k").Select
Selection.EntireColumn.Hidden = True
End Sub

I wanted to do this with a sql query, but I am a relative beginner at
vba/excel. Also, the IT department's backlog is pretty long, so I can't
get the proper command lines from them. Instead, I keyed off vlookup
and its associated errors. #Value indicates a match. I have already
proven that with many runs of my macro. It just takes forever!

ducky wrote:
> [email protected] wrote:
> > I am working on a macro to compare two lists of job numbers in two
> > separate workbooks. If there are any matches, those duplicate jobs are
> > flagged. I used vlookup to accomplish this. My problem is that my
> > table_array could have as many as 10000 cells. When I use vlookup to
> > check 200 different lookup_values, the processing time is, to say the
> > least, massive.
> >
> > Is there any way to speed up this process? I had thought of sorting the
> >
> > table_array, since there are a lot of blank cells in it. I guess my
> > main question is, how would I have one workbook sort a column in
> > another workbook?
> >
> >
> > Any help would be appreciated.
>
> Please post your code so we can look at it and offer suggestions. One
> thing i can say right now though is to investivge
> application.screenupdating = false
>
> AR