Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with speeding up vlookup macro
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with speeding up vlookup macro
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: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help speeding this up | Excel Programming | |||
Speeding up a Data Validation macro | Excel Programming | |||
help with speeding this up... | Excel Programming | |||
speeding up vlookup | Excel Programming | |||
speeding up a macro | Excel Programming |