PDA

View Full Version : Boost Macro running speed



curiousity88
08-06-2012, 08:45 AM
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

Kenneth Hobs
08-06-2012, 09:01 AM
Welcome to the forum! When posting code, please paste between VBA code tags.

See: http://vbaexpress.com/kb/getarticle.php?kb_id=1035

curiousity88
08-06-2012, 09:20 AM
in vba tags ...

Bob Phillips
08-06-2012, 12:23 PM
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
Const LOOKUP_FORMULA As String = _
"=VLOOKUP(P2,'[test - berekenen EP uitval_MEC June 2012.xlsx]EP per segm, ratecat en regio'!$AB:$AW," & _
"MOD(COLUMN()-16,22)+1,FALSE)"
Dim Cl As Range
Dim RKey As Range
Dim i As Long

With Sheets("US FL zonder UP")

Set RKey = .Range(Cells(2, 16), Cells(ActiveSheet.Cells(1, 1).SpecialCells(xlCellTypeLastCell).Row, 16))
With RKey.Offset(0, 1).Resize(, 21)

.Formula = LOOKUP_FORMULA
.Value = .Value
End With
End With
End Sub