Need help to fix vlookup function in macro
Dear all,
I've a macro with vlookup function to copy cells value if meet 1 criteria. However it didn't copy the cells correctly. What i want is if value in Sheet1.columnA same with Sheet2.columnB then copy value in Sheet2.ColumnA into Sheet1.columnB. Can anybody help to me fix my vlookup function? Below i illustrate the scenario. Thanks in advance for any help.
Sheet1
ColumnA ColumnB ColumnC
ABC123 fewr
VADE345 dfhfgh
BGR824 dfhgy
Sheet2
ColumnA ColumnB ColumnC
item1 ABC123 5453
item3 BGR824 32432
item2 VADE345 34546
Expected output (Sheet1)
ColumnA ColumnB ColumnC
ABC123 item1 fewr
VADE345 item2 dfhfgh
BGR824 item3 dfhgy
Current result which is wrong
ColumnA ColumnB ColumnC
ABC123 item1 fewr
VADE345 item1 dfhfgh
BGR824 item1 dfhgy
My macro code:
Sub Map()
Dim lr As Long
Dim lp As Long
Application.ScreenUpdating = False
Set Book = ActiveWorkbook
Windows("Book1.xls").Activate
Sheets("Sheet2").Select
Range("A2").Select
lp = Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Sheet1").Select
Range("A2").Select
l r = Cells(Rows.Count, 1).End(xlUp).Row
With Range("B2:B" & lr)
.Formula = "=VLOOKUP(A2,Sheet2!$A$2:$B$" & lp & ",1,TRUE)"
.value = .value
End With
End Sub
|