Help with speeding up a vlookup macro

  • Thread starter Thread starter tony.martinelli
  • Start date Start date
T

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.
 
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.
 
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)
Sharkbait said:
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 said:
you might consider using VBA FIND. Look in the vba help index.
Click to expand...
Click to expand...
 
Back
Top