Hello there, I want to do Vlookup in VBA. I have two tabs: sheet1- I want the Vlookup results to show in Column B here. sheet 2 - the source data. Is that possible to do vlookup in sheet1 until column A has an empty cell?

So sheet1 is :-

Column A Column B T100 T120 T200 T120 T200 T100 T100 T100 T100 T100sheet 2 is :-

Column A Column B T100 Student T120 Teacher T200 Workers

message edited by suzysss

✔ Best Answer

I just stumbled across this tip: Instead of using all of those extra quotes when trying to create an empty string in VBA, you can use the TEXT function. (I typically screw up the extra quotes at least once when trying to build formulas like the one we're using here.)

The TEXT function obviously remains in the formula, so it looks kind of strange in the spreadsheet, but it makes the coding a bit more fool-proof - says this fool. ;-)

Sub CreateVlookUp2() Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row).Formula = _ "=IFERROR(VLOOKUP(A1,Sheet2!$A$1:$B$3,2,0),TEXT(,))" End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Try this vba code, I am assuming your data starts in row 1 Also assuming that your lookup table is from A1 to B3

Sub CreateVlookUp() Dim Bcell As Range For Each Bcell In Range("A1", Range("A" & Rows.Count).End(xlUp)) Bcell.Offset(0, 1).Formula = "=Vlookup(" & Bcell.Address & ",Sheet2!A1:B3,2,False)" Next Bcell End Sub

message edited by AlwaysWillingToLearn

genius!!! thanks for your help!

No worries glad it worked :)

Just a slight modification to the code for instances where you may have a break in your data for example A B 1 T100 2 T120 3 T200 4 T100 5 6 T200The code will ignore row 5 rather than enter a vlookup which will cause an error and be displayed as N/A

Sub CreateVlookUp() Dim Bcell As Range For Each Bcell In Range("A1", Range("A" & Rows.Count).End(xlUp)) If Bcell <> vbNullString Then Bcell.Offset(0, 1).Formula = "=Vlookup(" & Bcell.Address & ",Sheet2!A1:B3,2,False)" End If Next Bcell End Sub

message edited by AlwaysWillingToLearn

Thanks for your explanation !!

hello AlwayswillingToLearn...... I just tried the code again.. I found it's really slow for running the VBA vlookup as my column A in sheet 1 has more than 50,000 line items.. is there any way to make it faster?

Maybe this will help, please test Sub CreateVlookUp() Dim Bcell As Range Application.ScreenUpdating= False For Each Bcell In Range("A1", Range("A" & Rows.Count).End(xlUp)) If Bcell <> vbNullString Then Bcell.Offset(0, 1).Formula = "=Vlookup(" & Bcell.Address & ",Sheet2!A1:B3,2,False)" End If Next Bcell Application.ScreenUpdating = True End Sub

Why not fill in the formulas all at once instead of looping through the range? Add the IFERROR function to eliminate any #N/A errors. For 100K cells in Column A, the Loop averaged about 1 minute, even with ScreenUpdating set to False. Without the Loop, the run time was practically instantaneous.

Sub CreateVlookUp2() Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row).Formula = _ "=IFERROR(VLOOKUP(A1,Sheet2!$A$1:$B$3,2,0),"""")" End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Why not fill in the formulas all at once instead of looping through the range? Because I didn't think of that :)

nice work Derby always a VBA savage!!!!

I just stumbled across this tip: Instead of using all of those extra quotes when trying to create an empty string in VBA, you can use the TEXT function. (I typically screw up the extra quotes at least once when trying to build formulas like the one we're using here.)

The TEXT function obviously remains in the formula, so it looks kind of strange in the spreadsheet, but it makes the coding a bit more fool-proof - says this fool. ;-)

Sub CreateVlookUp2() Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row).Formula = _ "=IFERROR(VLOOKUP(A1,Sheet2!$A$1:$B$3,2,0),TEXT(,))" End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Thanks Alwayswillingtolearn & DerbyDadd!! you both super starts! I've just done a test, for 1,000 lines, use the original code took about 18 seconds... the new code above only need less than 1 second... I am so grateful ... thanks for sharing ^^

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History