Excel workbook hangs up when vlookup vba is executed

%3CLINGO-SUB%20id%3D%22lingo-sub-3500347%22%20slang%3D%22en-US%22%3EExcel%20workbook%20hangs%20up%20when%20vlookup%20vba%20is%20executed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3500347%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20coded%20a%20simple%20vlookup%20vba%20code%3B%20it%20works%20but%20whenever%20i%20try%20to%20execute%20it%2C%20the%20whole%20workbook%20got%20hanged.%20Is%20there%20any%20way%20to%20fix%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVlookup%20is%20executed%20with%20244k%20rows%20of%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20sVlook()%0A%0ADim%20ws%20As%20Worksheet%0ADim%20agingwb%20As%20Workbook%0ADim%20lastRow%20As%20Long%0ADim%20i%20As%20Long%0A%0AWorkbooks.Open%20(%22C%3A%5CUsers%5Chrhquek%5CDesktop%5CSOA%20-%2010062022.xlsx%22)%0A%0ASet%20ws%20%3D%20Sheet2%0ASet%20agingwb%20%3D%20ActiveWorkbook%0AThisWorkbook.Activate%0A%0Aws.Range(%22AA1%22).EntireColumn.Insert%0Aws.Range(%22AA1%22).Value%20%3D%20%22Asset_Status%22%0A%0AlastRow%20%3D%20ws.Cells(ws.Rows.Count%2C%20%22Z%22).End(xlUp).Row%0A%0AWith%20Application%0A%0AFor%20i%20%3D%202%20To%20lastRow%0A%0ARange(%22AA%22%20%26amp%3B%20i).Value%20%3D%20.IfNa(.VLookup(Range(%22A%22%20%26amp%3B%20i).Value%2C%20agingwb.Sheets(%22Page1%22).Range(%22C%3AQ%22)%2C%2015%2C%20False)%2C%20%22%22)%0A%0ANext%20i%0A%0AEnd%20With%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3500347%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3500419%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20workbook%20hangs%20up%20when%20vlookup%20vba%20is%20executed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3500419%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1393409%22%20target%3D%22_blank%22%3E%40hrh_dash%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20notice%20that%20lastRow%20is%20the%20last%20used%20row%20on%20Sheet2%20in%20the%20workbook%20that%20you%20open.%3C%2FP%3E%0A%3CP%3EBut%20you%20use%20it%20in%20a%20loop%20For%20i%20%3D%202%20To%20lastRow%20to%20fill%20cells%20on%20a%20sheet%20in%20the%20macro%20workbook.%20Is%20that%20really%20what%20you%20want%3F%20You'd%20be%20processing%20244%2C000%20rows!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3500545%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20workbook%20hangs%20up%20when%20vlookup%20vba%20is%20executed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3500545%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%2C%20i%20know%20its%20crazy%20but%20is%20it%20possible%3F%20the%20reason%20is%20because%20i%20don't%20want%20other%20to%20see%20the%20formula%20and%20wish%20to%20lock%20the%20column%20so%20that%20no%20one%20could%20amend%20anything%20in%20that%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3501215%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20workbook%20hangs%20up%20when%20vlookup%20vba%20is%20executed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3501215%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1393409%22%20target%3D%22_blank%22%3E%40hrh_dash%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%20why%20do%20you%20use%20the%20last%20used%20row%20on%20the%20other%20sheet%20in%20a%20loop%20for%20the%20sheet%20in%20the%20macro%20workbook%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3502604%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20workbook%20hangs%20up%20when%20vlookup%20vba%20is%20executed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3502604%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%2C%26nbsp%3B%3CSPAN%3E%26nbsp%3B%2C%20lastRow%20is%20actually%20intended%20for%20the%20macro%20workbook%20with%20244k%20rows.%20Hence%2C%20the%20for%20loop%20to%20execute%20the%20vlookup%20from%20row%202%20to%20the%20last%20blank%20row.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3504603%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20workbook%20hangs%20up%20when%20vlookup%20vba%20is%20executed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3504603%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1393409%22%20target%3D%22_blank%22%3E%40hrh_dash%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELooping%20through%20244K%20rows%20will%20take%20a%20LOT%20of%20time!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3504820%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20workbook%20hangs%20up%20when%20vlookup%20vba%20is%20executed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3504820%22%20slang%3D%22en-US%22%3EI%20guess%20the%20only%20way%20is%20to%20manually%20enter%20in%20the%20formula.%20Thanks%20for%20the%20help%20anyway!%20Much%20appreciated!%3C%2FLINGO-BODY%3E
Contributor

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

@hrh_dash 

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!

@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.

@hrh_dash 

But why do you use the last used row on the other sheet in a loop for the sheet in the macro workbook?

@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.

 

@hrh_dash 

Looping through 244K rows will take a LOT of time!

I guess the only way is to manually enter in the formula. Thanks for the help anyway! Much appreciated!