Solved VBA Vlookup until empty cell

June 20, 2017 at 03:26:03
Specs: Windows 7
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	
T100	

sheet 2 is :-

Column A	       Column B
T100	                Student
T120	                Teacher
T200	                Workers


message edited by suzysss


See More: VBA Vlookup until empty cell

Reply ↓  Report •

✔ Best Answer
June 27, 2017 at 07:15:44
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



#1
June 20, 2017 at 03:46:01
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


Reply ↓  Report •

#2
June 20, 2017 at 03:56:04
genius!!! thanks for your help!

Reply ↓  Report •

#3
June 20, 2017 at 05:30:56
No worries glad it worked :)

Reply ↓  Report •

Related Solutions

#4
June 20, 2017 at 06:59:31
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      T200  

The 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


Reply ↓  Report •

#5
June 26, 2017 at 22:59:42
Thanks for your explanation !!

Reply ↓  Report •

#6
June 27, 2017 at 03:31:05
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?

Reply ↓  Report •

#7
June 27, 2017 at 03:42:31
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


Reply ↓  Report •

#8
June 27, 2017 at 06:13:52
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


Reply ↓  Report •

#9
June 27, 2017 at 06:59:01
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!!!!


Reply ↓  Report •

#10
June 27, 2017 at 07:15:44
✔ 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


Reply ↓  Report •

#11
June 27, 2017 at 22:37:30
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 ^^


Reply ↓  Report •

Ask Question