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

Ann Ooi

New Member
Joined
Jun 12, 2020
Messages
40
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
85
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,407
I'm not sure how much difference it would make but try putting your code between these two lines:
VBA Code:
Application.Calculation = xlCalculationManual
'your code here
Application.Calculation = xlCalculationAutomatic
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,226
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

New Member
Joined
Jun 12, 2020
Messages
40
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I'm not sure how much difference it would make but try putting your code between these two lines:
VBA Code:
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

New Member
Joined
Jun 12, 2020
Messages
40
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,226
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jun 12, 2020
Messages
40
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,226
Office Version
  1. 365
Platform
  1. Windows
Do you want to show an error of some sort in R if there is no match?
 

Ann Ooi

New Member
Joined
Jun 12, 2020
Messages
40
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,873
Messages
5,655,737
Members
418,234
Latest member
jdorfma

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top