Hi,
i have the below macro in one of the file which has more than 100,000+ records.
this macro is taking approx. 10 minutes to run.
is there any way we can speed up the process?
Any suggestion will be much apprciated.
i have the below macro in one of the file which has more than 100,000+ records.
this macro is taking approx. 10 minutes to run.
is there any way we can speed up the process?
Any suggestion will be much apprciated.
VBA Code:
Sub VlookupLocation()
Dim authorWs As Worksheet, detailsWs As Worksheet
Dim authorsLastRow As Long, detailsLastRow As Long, x As Long
Dim dataRng As Range
Set authorWs = ThisWorkbook.Worksheets("Unmatched GRN Report")
Set detailsWs = ThisWorkbook.Worksheets("Loc_Status")
authorsLastRow = authorWs.Range("A" & Rows.Count).End(xlUp).Row
detailsLastRow = detailsWs.Range("A" & Rows.Count).End(xlUp).Row
Set dataRng = detailsWs.Range("A2:L" & detailsLastRow)
For x = 2 To authorsLastRow
On Error Resume Next
If authorWs.Range("AD" & x).Value = "" Then
authorWs.Range("AD" & x).Value = Application.WorksheetFunction.VLookup( _
authorWs.Range("G" & x).Value, dataRng, 2, False)
authorWs.Range("AG" & x).Value = Application.WorksheetFunction.VLookup( _
authorWs.Range("G" & x).Value, dataRng, 5, False)
authorWs.Range("AI" & x).Value = Application.WorksheetFunction.VLookup( _
authorWs.Range("G" & x).Value, dataRng, 7, False)
authorWs.Range("AL" & x).Value = Application.WorksheetFunction.VLookup( _
authorWs.Range("G" & x).Value, dataRng, 10, False)
authorWs.Range("AM" & x).Value = Application.WorksheetFunction.VLookup( _
authorWs.Range("G" & x).Value, dataRng, 11, False)
authorWs.Range("AN" & x).Value = Application.WorksheetFunction.VLookup( _
authorWs.Range("G" & x).Value, dataRng, 12, False)
Else
End If
Next x
End Sub