PDA

View Full Version : Boost Macro running speed



curiousity88
08-05-2012, 11:52 PM
Hello everybody,

The macro below takes too much time running.
Does anybody know how I can have it run faster?
I have 8,600 lines and more than 34,000 lines in my lookup table.

Thank you for you help!

MACRO CODE
-------------


Sub Vlookup1()
' in order for the macro to work, the file "berekenen EP uitval_MEC June 2012.xlsx"
' has to be openen before running the macro
Sheets("US FL zonder UP").Select
Dim Cl As Range
Dim RKey As Range
Set RKey = ActiveSheet.Range(Cells(2, 16), Cells(ActiveSheet.Cells(1, 1).SpecialCells(xlCellTypeLastCell).Row, 16))

For Each Cl In RKey.Cells
For i = 1 To 21
On Error Resume Next
Cl.Offset(0, i) = WorksheetFunction.Vlookup(Cl.Value, Workbooks("test - berekenen EP uitval_MEC June 2012.xlsx").Worksheets("EP per segm, ratecat en regio").Range("$AB:$AW"), i + 1, False)
' change the name of the workbook to adapt for each MEC

Next i
Next Cl
End Sub