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

On Wed, 24 Oct 2012 02:13:35 +0000, ixara wrote:


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


I cannot critique your current macro as it doesn't run on my machine, and making some changes to get it to run doesn't do anything.
However, some of the problems in your macro include
Lack of variable declaration.
This can be forced by selecting Require Variable Declaration under Tools/Options, which will place Option Explicit at the start of every macro module
Irrelevant activating and selecting of target sheets, workbooks, ranges. There is almost never a need to do this.
Inappropriate use of VLOOKUP. If I understand you correctly, you want to look at Column B on Sheet 2, and return the match from Column A on Sheet 2. (If that is not the case then I misunderstood). See HELP for VLOOKUP to understand why that cannot work.

If I understand what you want to do correctly, here is one way to do that:

=======================================
Option Explicit
Sub FillInSheetOne()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim r1 As Range, r2 As Range
Dim v1 As Variant
Dim c As Range
Dim i As Long, j As Long
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

With ws1
Set r1 = .Range("A1", .Cells(.Rows.Count, "C").End(xlUp))
End With
With ws2
Set r2 = .Range("A1", .Cells(.Rows.Count, "C").End(xlUp))
End With

v1 = r1
For i = LBound(v1, 1) To UBound(v1, 1)
With r2.Columns(2)
Set c = .Find(what:=v1(i, 1), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not c Is Nothing Then v1(i, 2) = c.Offset(columnoffset:=-1)
End With
Next i

r1 = v1

End Sub
=============================

If you want to write a formula, rather than a value, into sheet1, column B, then look at using an =INDEX(MATCH.... construct so you can return the desired column.