Help with speeding up a vlookup macro

[tony.martinelli]

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.

 

[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]

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
(e-mail address removed)

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.

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