VBA Vlookup slow - how can I speed this up?

[dturgel]

Hi,

This vlookup is taking me 7 minutes to run - way too slow, anyone have any ideas for improving the speed?

The following code is designed to leave a formula in a cell in the active worksheet. The formula references a key cell in this active workbook (cell A11 but referred to in the following code as R[-18]C1) and then takes the value from that key cell to look up a value in a worksheet in a different workbook. Currently the code is set up to leave formulas in 52 cells in the active workbook but eventually I want to expand that to over 100 cells. (Previously I had the other worksheet as just another tab in my active workbook and worked off the indirect function in excel and it took less than 1 second to look up all the values. But now I need to look it up from the other workbook. I think a potential solution would be to import the entire row first into my active worksheet and then set up a simple macro to distribute the values from the active worksheet - if so, how do you import/export entire rows using VBA code?)

Windows("Copy of Mkt Curve Tool Working Copy SW v3.xlsm").Activate
Range("AKExpPrem1").Select
Range("AKExpPrem1").FormulaR1C1 = _
"=VLOOKUP(R[-18]C1,'[2012 to 2016 bound new test VBA.xlsm]2015 CLASH'!R7C1:R500C112, Column('[2012 to 2016 bound new test VBA.xlsm]2015 CLASH'!C60), FALSE)"

Daniel

 

[revcanon]

As far as timing goes, you may try adding this to the top:

        Application.ScreenUpdating = False

And this to the bottom:

        Application.ScreenUpdating = True

Sometimes this speeds things up.

I also have a spreadsheet with VBA doing Vlookups and it seems to lag a bit while doing it. 7 minutes, however, does seem a little excessive to me.

As far as making it look at another worksheet, you could use VBA to open the target worksheet at the time of running the macro and copy and paste data from one to the other using something like this

    Set wb1 = ThisWorkbook
    Set ws1 = wb1.ActiveSheet
    ' Select Report
        ChDir "Y:\our\Path\Here"
        NewReport= Application.GetOpenFilename("All Excel Files (*.xl*)," & _
        "*.xl*", 1, "Select Report", "Open", False)
            If TypeName(NewReport) = "Boolean" Then
                MsgBox "No file selected. Process will end."
                Exit Sub
            End If
        Workbooks.Open NewReport
    Set wb2 = ActiveWorkbook

After it is open, you would need to define what you want copied from wb2 and where you want it pasted to wb1.

 

[dturgel]

Thanks! I will try and revert...

 

[dturgel]

I tried it and it's not going to work - the workbook we are looking in is shared and password protected so it updates and asks for a password each time - it just dawned on me that this must be a reason why the macro is so slow. Appreciate the idea!

 

[shg]

If you sort the lookup table by the lookup column, it will go significantly faster. Change the last argument of the VLOOKUP function to TRUE and Excel will do a binary search instead of a linear search.

 

[dturgel]

I recalled reading somewhere that .select always slows things down so when I removed it from the code it did the trick. Here's what the new code looks like:

Windows("Copy of Mkt Curve Tool Working Copy SW v3.xlsm").Activate
Range("MCTSI2RenewalL1").FormulaR1C1 = _
"=IFERROR(VLOOKUP(R11C1,'[2012 to 2016 bound new test VBA.xlsm]2016 CLASH'!R7C1:R500C112, Column('[2012 to 2016 bound new test VBA.xlsm]2016 CLASH'!C3), FALSE),""NA"")"