Help with speeding up a vlookup macro

[[email protected]]

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.

[Don Guillett]

you might consider using VBA FIND. Look in the vba help index.

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
>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.
>

[Sharkbait]

Here's my code.

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!

Thanks, I'll try that.

Don Guillett wrote:
> you might consider using VBA FIND. Look in the vba help index.
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> <[email protected]> wrote in message
> news:[email protected]...
> >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.
> >

[Don Guillett]

let's see what we can do to clean this up

Sub ShippedWIP()
Columns("k").Insert
Range("B26").End(xlDown).Offset(0, 9)= 1
'======================
'Range("k26").FormulaR1C1 = _
=VLOOKUP(RC[-9],'[crViewer.xls]Sheet1'!R1C2:R1000C2,0,FALSE)"

Set x = Workbooks("crviewer.xls").Sheets("Sheet1").Columns(3)
range("k26") = x.Find(range("b26"))
'=======================
Range(Range("k26"), Range("k26").End(xlDown)).FillDown
Columns("k").AutoFit

'-------------------------------
'not quite sure what is going on here
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").Hidden = True
End Sub




--
Don Guillett
SalesAid Software
[email protected]
"Sharkbait" <[email protected]> wrote in message
news:[email protected]...
> Here's my code.
>
> 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!
>
> Thanks, I'll try that.
>
> Don Guillett wrote:
>> you might consider using VBA FIND. Look in the vba help index.
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> <[email protected]> wrote in message
>> news:[email protected]...
>> >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.
>> >
>