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]

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:

Sheet2 (Before running the macro):
View attachment 103566

Sheet2 (After running the macro):
View attachment 103565
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.

 

[Georgiboy]

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

 

[kevin9999]

Another option:

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,"""",0)"
        .Value = .Value
    End With
End Sub

 

[Georgiboy]

@kevin9999

How will that deal with the last bit of the request:

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?

 

[kevin9999]

@kevin9999

How will that deal with the last bit of the request:

Oops! Didn't notice that bit. It's been a long day

 

[kevin9999]

Try again...

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
End Sub

 

[kevin9999]

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

 

[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

 

[BBCC0000]

Thank you everyone for your kind help. Hope you guys have a nice day!!