Data Range too huge and VBA takes longer time to complete run formula

[Ann Ooi]

I have this code to insert vlookup formula into a range of data, due to the lines is over 50K, it takes very long time to complete the whole process. Anyway to avoid this running time and able to apply the formula successfully?

Sub LookupPrime()

    Dim r As Range
    Dim s As Range
    Dim lastA As Long
                
    Application.ScreenUpdating = False
    
    ThisWorkbook.Worksheets("InvList").Select
     
    lastA = Range("A" & Rows.Count).End(xlUp).Row
         
    Range("R1").Value = "lookup"
    Range("S1").Value = "Wafer Type"
    
    Set r = Range("R2" & ":R" & lastA)
    r.Formula = "=VLOOKUP(Q2,PRef!A:B,2,0)"
    r.Value = r.Value
    
    Set s = Range("S2" & ":S" & lastA)
    s.Formula = "=IF(ISERROR(R2),P2,R2)"
    s.Value = s.Value

    Application.ScreenUpdating = True

End Sub

 

[Dermot]

1. If you have Excel365 you could use a spill function for the lookup that is possibly much faster

2. Vlookup uses linear search. If you sort your data and use an approximate search instead of exact (ie if it can't find an exact match, it uses the nearest), this search is binary and can be upwards of 100x faster (see http://www.tushar-mehta.com/publish_train/xl_vba_cases/match-exact-vs-binary.htm). So if you are confident there is always a match, this is probably the fastest approach

3. If you need to look for an exact match, the Mehta article I linked above talks about doing this using two formulae by doing the approximate search first. It explains like this
" Suppose a 100,000 element unsorted lookup_array is in B with the lookup values in C starting with row 1. Then, after sorting lookup_array in ascending order, enter in D1 =MATCH(C1,$B$1:$B$100000,1) and in E1 =IF(INDEX($B$1:$B$100000,D1)=C1,D1,NA()). Cell E1 will now have the same result as if we had used =MATCH(C1,$B$1:$B$100000,0)." This is apparently about 10x faster than the normal Lookup.

 

[mumps]

I'm not sure how much difference it would make but try putting your code between these two lines:

Application.Calculation = xlCalculationManual
'your code here
Application.Calculation = xlCalculationAutomatic

 

[Fluff]

How about

Sub AnnOoi()
   Dim Ary As Variant, Oary As Variant
   Dim i As Long
   Dim dic As Object
  
   Ary = Sheets("PRef").Range("A1").CurrentRegion.Value2
   Set dic = CreateObject("scripting.dictionary")
  dic.CompareMode=1
   For i = 1 To UBound(Ary)
      dic.Item(Ary(i, 1)) = Ary(i, 2)
   Next i
     
   With Sheets("InvList")
      Ary = .Range("P2:Q" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   ReDim Oary(1 To UBound(Ary), 1 To 2)
   For i = 1 To UBound(Ary)
      If dic.exists(Ary(i, 2)) Then
         Oary(i, 1) = dic.Item(Ary(i, 2))
         Oary(i, 2) = dic.Item(Ary(i, 2))
      Else
         Oary(i, 2) = Ary(i, 1)
      End If
   Next i
   Sheets("InvList").Range("R2").Resize(i - 1, 2).Value = Oary
End Sub

 

[Ann Ooi]

I'm not sure how much difference it would make but try putting your code between these two lines:

Application.Calculation = xlCalculationManual
'your code here
Application.Calculation = xlCalculationAutomatic

Hi mumps, Thank you so much, but it's still spinning and not well performing.

 

[Ann Ooi]

How about

Sub AnnOoi()
   Dim Ary As Variant, Oary As Variant
   Dim i As Long
   Dim dic As Object
 
   Ary = Sheets("PRef").Range("A1").CurrentRegion.Value2
   Set dic = CreateObject("scripting.dictionary")
  dic.CompareMode=1
   For i = 1 To UBound(Ary)
      dic.Item(Ary(i, 1)) = Ary(i, 2)
   Next i
    
   With Sheets("InvList")
      Ary = .Range("P2:Q" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   ReDim Oary(1 To UBound(Ary), 1 To 2)
   For i = 1 To UBound(Ary)
      If dic.exists(Ary(i, 2)) Then
         Oary(i, 1) = dic.Item(Ary(i, 2))
         Oary(i, 2) = dic.Item(Ary(i, 2))
      Else
         Oary(i, 2) = Ary(i, 1)
      End If
   Next i
   Sheets("InvList").Range("R2").Resize(i - 1, 2).Value = Oary
End Sub

Hi Fluff, Thank you so much, it's working fine and no issue of time delay. But instead of the formula apply in column R, it's apply in column S, what I can do to have the output shows in column R?

 

[Fluff]

If there is nothing in col R then there are no matches.
My code does exactly the same as the code you posted. Other than it does not put #N/A into col R if there is no match.
Although that can be changed if needed.

 

[Ann Ooi]

If there is nothing in col R then there are no matches.
My code does exactly the same as the code you posted. Other than it does not put #N/A into col R if there is no match.
Although that can be changed if needed.

Hi Fluff, noted, I guess so. I overlooked that, thank you very much for your great help.

rgds,
Ann

 

[Fluff]

Do you want to show an error of some sort in R if there is no match?

 

[Ann Ooi]

Do you want to show an error of some sort in R if there is no match?

Hi Fluff, initial I remain the error, so that easy for me to input the formula to return the value, but your code return the same result and it's much presentable and clean. This is great! Thank you so much.

rgds,
Ann