V Lookup Loop taking ages to run

Probably yes, there are ways to make it more efficient but without more information or sample file you won't get much help. Refer to the top yellow banner on how to post a sample file - Make the sample file with the exact file format so you don't have to hassle trying to amend the provided code

Thank you, I've amended the post with the sample file and my code. Please assist.

Hi,

this kind of issue often comes from using a 'cell-by-cell' loop …

Just explain in a concise manner what you want to achieve. Your attachment does not tell us much.
But before anything, at the top click on "Forum Rules" and read #2.

Hi,

Thank you for sharing the code. I'm trying to use it in my file but fail to understand. I've now attached my file. Could you please assist in it?

Hi,

I have been trying to reply to you but this site was down for long. I tried the code but it is still not working.
I'm attaching the picture of what I get. Below is the code, I have made few tweaks as per requirement.
For e.g. Instead of Col. O, I used Col. N. Vlookup column is "H". Please advise. eagerly waiting for your response. Thank you.

Sub test()   ' Macro for 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, LR As Long, columns As Variant



Set main_wbk = Workbooks("CMDB_Production_File.xlsb")
Set main_sht = main_wbk.Sheets("CMDB - Raw Data")
Set sht1 = main_wbk.Sheets("Master List Asset Categorzation")

main_wbk.Activate

LR = main_wbk.Sheets("Master List Asset Categorzation").range("a" & Rows.Count).End(xlUp).Row

With Sheets("CMDB - Raw Data").Cells(1).CurrentRegion
    With .columns("N").Offset(1).Resize(.Rows.Count - 1)
         .Formula = "=Vlookup(H2, 'sht1'!$A$1:$B$" & LR & ",2,False)"
         .Value = .Value
    End With
End With

End Sub

Sub test()
    Dim LR As Long
    LR = Sheets("Master List Asset Categorzation").Range("a" & Rows.Count).End(xlUp).Row
    With Sheets("CMDB - Raw Data").Cells(1).CurrentRegion
        With .Columns("O").Offset(1).Resize(.Rows.Count - 1)
            .Formula = "=VLOOKUP(I2,'Master List Asset Categorzation'!$A$1:$B$" & LR & ",2,FALSE)"
            .Value = .Value
        End With
    End With
End Sub

Because you changed the column reference, it returns #N/A error with your attached workbook.

Sub test()   ' Macro for Categorization

Dim main_wbk As Workbook, main_sht As Worksheet, sht1 As Worksheet, LR As Long

Set main_wbk = Workbooks("CMDB_Production_File.xlsb")
Set main_sht = main_wbk.Sheets("CMDB - Raw Data")
Set sht1 = main_wbk.Sheets("Master List Asset Categorzation")

LR = main_wbk.Sheets("Master List Asset Categorzation").Range("a" & Rows.Count).End(xlUp).Row

With main_sht.Cells(1).CurrentRegion
    With .Columns("N").Offset(1).Resize(.Rows.Count - 1)
         .Formula = "=Vlookup(H2, '" & sht1.Name & "'!$A$1:$B$" & LR & ",2,False)"
         .Value = .Value
    End With
End With
End Sub

Thank you so much. It worked. Thank you, thank you

Please assist one more time in making me this code understand so that I can use it for my other columns also. I understand that in column N it will read
values for each cell (offset(1)). Post this, if you could please make me understand the code, I will be really thankful

With .Columns("N").Offset(1).Resize(.Rows.Count - 1)
.Formula = "=Vlookup(H2, '" & sht1.Name & "'!$A$1:$B$" & LR & ",2,False)"
.Value = .Value

Sub test()   ' Macro for Categorization

Dim main_wbk As Workbook, main_sht As Worksheet, sht1 As Worksheet, LR As Long

Set main_wbk = Workbooks("CMDB_Production_File.xlsb")
Set main_sht = main_wbk.Sheets("CMDB - Raw Data")
Set sht1 = main_wbk.Sheets("Master List Asset Categorzation")

LR = main_wbk.Sheets("Master List Asset Categorzation").Range("a" & Rows.Count).End(xlUp).Row

With main_sht.Cells(1).CurrentRegion
    msgbox .address  '<--- range address of whoele data range
    With .Columns("N").Offset(1).Resize(.Rows.Count - 1)
         msgbox .address  <--- range address of where formula should be placed
         'when Offset(1), it refers from 2nd row of the range to the one row below the data range, that is out side of data range,
         ' so you need to Resize the range by .Resize(.Rows.Count -1), so that it now refers to the 2nd row to the last row of the data range. 
         .Formula = "=Vlookup(H2, '" & sht1.Name & "'!$A$1:$B$" & LR & ",2,False)"
         .Value = .Value   '<--- convert formula to value
    End With
End With
End Sub

HTH