Forum Discussion
Excel workbook hangs up when vlookup vba is executed
I have coded a simple vlookup vba code; it works but whenever i try to execute it, the whole workbook got hanged. Is there any way to fix this?
Vlookup is executed with 244k rows of data.
Sub sVlook()
Dim ws As Worksheet
Dim agingwb As Workbook
Dim lastRow As Long
Dim i As Long
Workbooks.Open ("C:\Users\hrhquek\Desktop\SOA - 10062022.xlsx")
Set ws = Sheet2
Set agingwb = ActiveWorkbook
ThisWorkbook.Activate
ws.Range("AA1").EntireColumn.Insert
ws.Range("AA1").Value = "Asset_Status"
lastRow = ws.Cells(ws.Rows.Count, "Z").End(xlUp).Row
With Application
For i = 2 To lastRow
Range("AA" & i).Value = .IfNa(.VLookup(Range("A" & i).Value, agingwb.Sheets("Page1").Range("C:Q"), 15, False), "")
Next i
End With
End Sub
6 Replies
I notice that lastRow is the last used row on Sheet2 in the workbook that you open.
But you use it in a loop For i = 2 To lastRow to fill cells on a sheet in the macro workbook. Is that really what you want? You'd be processing 244,000 rows!
HansVogelaar , i know its crazy but is it possible? the reason is because i don't want other to see the formula and wish to lock the column so that no one could amend anything in that column.
But why do you use the last used row on the other sheet in a loop for the sheet in the macro workbook?
