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

 

[Fluff]

You're welcome & thanks for the feedback.