Hi,
I've been trying to run a code which has data for approx. 35,000 rows and there is a v-lookup loop which is giving the correct results. But the challenge is, this specific module takes more than an hour (1.5 hrs) to run. Is there a way to make the code more efficient?
What I need: Col. N gets updated in CMDB_Raw Data sheet.
How: Col. A of Raw Data sheet looked up in sheet- "Master List Asset Categorization" and a matching value from Col. B is populated in Col. N of raw data sheet
Attaching the file. Also, here is my code:
[CODE]Sub Categorization()
Dim lastRow As Long, k As Long, wbk As Workbook, main_wbk As Workbook, lastrow1 As Long, range As range
Dim main_sht As Worksheet, Sheet1 As Worksheet, sht1 As Worksheet, sepr As String
Set main_wbk = Workbooks("CMDB_Production_File.xlsb")
Set main_sht = main_wbk.Sheets("CMDB - Raw Data")
main_wbk.Activate
lastRow = main_wbk.Sheets("Master List Asset Categorzation").range("a1").End(xlDown).Row
k = 2
Do While main_sht.Cells(k, 1).Value <> ""
On Error Resume Next
main_sht.Cells(k, 14).Value = WorksheetFunction.VLookup(main_sht.Cells(k, 8).Value, _
Sheets("Master List Asset Categorzation").range("a1:b" & lastRow), 2, 0)
k = k + 1
Loop
MsgBox ("Press Categorization button")
End Sub
[CODE]
Bookmarks