VLOOKUP in VBA

[BBCC0000]

Dear all,

Thanks for reading this thread. My question is:

First, I will paste some Number in Sheet2 row A, may I know how to create a macro that can VLOOKUP their Code from Sheet1?
If the Number isn't available in Sheet1, can it paste all the missing Number in Sheet2 to the last row of Number of Sheet1?

Sheet1:
View attachment 103564

Sheet2 (Before running the macro):

Sheet2 (After running the macro):

The function looks like: =VLOOKUP(A2,Sheet1!A:B,2,0)

Is it any possible way to create a macro like this?

I appreciate your time to read this question. Thanks in advance.

 

[BBCC0000]

Perhaps the below will help:

Sub test()
    Dim rCell As Range
   
    For Each rCell In Sheet2.Range("A2:A" & Sheet2.Range("A" & Rows.Count).End(xlUp).Row)
        rCell.Offset(, 1) = Evaluate("VLOOKUP(" & rCell.Value & ",Sheet1!A:B,2,0)")
        If IsError(rCell.Offset(, 1)) Then
            Sheet1.Range("A" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1) = rCell.Value
        End If
    Next rCell
End Sub

Hi,

I found out that when I'm using this code, if there are two consecutive numbers in Sheet2 that it cannot be found in Sheet1, it will have error, stating "Object required".

After I added "On Error Resume Next", it worked well but it doesn't paste all the missing Number in Sheet2 to the last row of Number of Sheet1...

Could you please help me on this?

 

[BBCC0000]

Without the duplicates

Sub Xlookup_Sht1()
    With Worksheets("Sheet2").Range("B2:B" & Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row)
        .Formula = "=XLOOKUP(A2,Sheet1!A:A,Sheet1!B:B)"
        On Error Resume Next
        Worksheets("Sheet2").Range("B2:B" & Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).Offset(, -1).Copy Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .Value = .Value
    End With
    Worksheets("Sheet1").Range("A:B").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

When I'm using this, after I run the macro, it will paste all the missing Number in Sheet2 to the last row of Number of Sheet1, but in Sheet2 all the value will become "#NAME?"

 

[kevin9999]

Usually, the #NAME? error occurs when Excel doesn't recognise a function you're trying to use. Your profile says you have 365 so I assumed the XLOOKUP() function was available to you. Try typing into a cell =XLOO and see if Excel suggests XLOOKUP for you?

Anyhow, try the following instead using the trusty old Vlookup instead:

Sub Vlookup_Sht1()
    With Worksheets("Sheet2").Range("B2:B" & Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row)
        .Formula = "=VLOOKUP(A2,Sheet1!A:B,2,FALSE)"
        On Error Resume Next
        Worksheets("Sheet2").Range("B2:B" & Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).Offset(, -1).Copy Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .Value = .Value
    End With
    Worksheets("Sheet1").Range("A:B").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

 

[BBCC0000]

Usually, the #NAME? error occurs when Excel doesn't recognise a function you're trying to use. Your profile says you have 365 so I assumed the XLOOKUP() function was available to you. Try typing into a cell =XLOO and see if Excel suggests XLOOKUP for you?

Anyhow, try the following instead using the trusty old Vlookup instead:

Sub Vlookup_Sht1()
    With Worksheets("Sheet2").Range("B2:B" & Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row)
        .Formula = "=VLOOKUP(A2,Sheet1!A:B,2,FALSE)"
        On Error Resume Next
        Worksheets("Sheet2").Range("B2:B" & Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).Offset(, -1).Copy Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .Value = .Value
    End With
    Worksheets("Sheet1").Range("A:B").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

Yes and sorry, my pc doesn't recognize XLOOKUP() function... And thanks again! It works well :D

 

[kevin9999]

Yes and sorry, my pc doesn't recognize XLOOKUP() function... And thanks again! It works well :D

Glad we could help, and thanks for the feedback