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
365
Platform
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
73
Office Version
365
Platform
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
9,362
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
44,644
Office Version
365
Platform
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
365
Platform
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
365
Platform
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
44,644
Office Version
365
Platform
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
365
Platform
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
44,644
Office Version
365
Platform
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
365
Platform
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,175
Messages
5,509,612
Members
408,743
Latest member
leen1234

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top