Why is my vba vlookup so slow!!

[alexaronson]

I need some help speeding up this macro. The area that is slowing me down is my For loop with the vlookup. lastRow of the loop is ~45,000 iterations. Normally, it takes only a minute to process in past macro uses. However, it is taking 15 minutes to complete which is just not right.
Here is what I have so far.

Sub UpdateCapacity()

Application.ScreenUpdating = False

Dim wbUC As Workbook 'this is the variable to keep this file's name
Dim wsDMDD As Worksheet 'this is hte variable to store the demand data worksheet
Dim wsInvD As Worksheet ' this is the variable to store the name of the Inventory data worksheet name

Dim i As Long 'this is row counter tracker
Dim j As Integer 'this is a column counter tracker
Dim lastRow As Long 'this is to track the column data
Dim lastRow2 As Long 'this is to track the column data while lastRow is in use
Dim a As Integer 'this variable is spare count variable
Dim lastColumn As Integer 'used to find the last column of data in the roll forward report
Dim strColName As Variant 'used to rename the column variable that lastColumn returns
Dim x As Long 'used to temporarily hold the looked out value

Set wbUC = ThisWorkbook
Set wsDMDD = wbUC.Worksheets("Dump Data Demand")
Set wsInvD = wbUC.Worksheets("Free Stock")

'Match Free Stock to Demand
i = 12
lastRow = wsDMDD.Range(strColName & Rows.Count).End(xlUp).Row
lastRow2 = wsInvD.Range("B" & Rows.Count).End(xlUp).Row

For i = i To lastRow
    On Error Resume Next
    x = WorksheetFunction.VLookup(wsDMDD.Cells(i, 3), wsInvD.Range("A1:B" & lastRow2), 2, False)
    If Err.Number = 0 Then wsDMDD.Cells(i, lastColumn + 1) = x
    x = 0
Next

Application.ScreenUpdating = True
End Sub

 

[Jerry Sullivan]

Hi,

For a dataset of 45,000 rows, your process will work much faster if you eliminate the loop
and instead place the VLOOKUP formula directly in each Cell of the Range using:
Range.FormulaR1C1

You can then either keep the formulas in place or replace the formulas with values...

With Range
 .Value = .Value
End With

 

[alexaronson]

JS411,

I am not sure how to do the R1C1 code. Can you give me an example that I can look at?

Thanks for the suggestion and your time.

-Alex

 

[obiron]

Don't put 45K VLOOKUP() calculations on your spreadsheet.
VLOOKUP is a volatile function and recalculates whenver ANY cell value changes and will kill your workbook

Can you re-write you VBA code to use the FIND or MATCH

If you do need to put the formulas into the spreadsheet use MATCH() and OFFSET() rather than VLOOKUP

Obiron

 

[Richard Schollar]

Don't put 45K VLOOKUP() calculations on your spreadsheet.
VLOOKUP is a volatile function and recalculates whenver ANY cell value changes and will kill your workbook

Incorrect I'm afraid - Vlookup is definitively not volatile. It will recalculate if changes are made to any of the ranges referenced in the Vlookup formula, however.

If you do need to put the formulas into the spreadsheet use MATCH() and OFFSET() rather than VLOOKUP

Obiron

OFFSET is volatile, however, so this wouldn't help.


OP, you could apply your formulas by doing this:

With wsDMDD
   .Range(.Cells(12,lastColumn + 1),.Cells(lastRow,lastColumn + 1)).FormulaR1C1 = _
   "=VLOOKUP(RC3,'" & wsInvD.Name & "'!R1C1:R" & lastRow2 & "C2,2,FALSE)"
End With

Note that it will take a while to calculate as 45k vlookups is a lot.