Jun 13 2022 07:35 AM
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
Jun 13 2022 07:52 AM
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!
Jun 13 2022 08:21 AM
@Hans Vogelaar , 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.
Jun 13 2022 11:14 AM
But why do you use the last used row on the other sheet in a loop for the sheet in the macro workbook?
Jun 13 2022 04:53 PM
@Hans Vogelaar , , lastRow is actually intended for the macro workbook with 244k rows. Hence, the for loop to execute the vlookup from row 2 to the last blank row.
Jun 14 2022 05:11 AM
Looping through 244K rows will take a LOT of time!
Jun 14 2022 06:00 AM