You may not need code at all.

=vlookup() looks like it would be perfect.

=if(iserror(vlookup(a1,[book2.xls]sheet1!a:b,2,false)),"",
vlookup(a1,[book2.xls]sheet1!a:b,2,false))

(all one cell)

Debra Dalgleish's has some notes you may like:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))


wrote:

Hi
I have two files. The first file contains a list which is a subset of a
list in another file. I need to pick values against the list in the
second file and place it in the first file.

First file
A
X
D
B

Second file
A 10
B 20
C 30
D 40

The First file on completion of the task should look like:
A 10
X
D 40
B 20

I am able to accomplish the task using the code below. The problem is
I'm not sure how to do a error check. This leads to a output like:

A 10
X 10
D 40
B 20

since the value 'X' does not exist in the second file.

Would appreciate very much if someone could provide a fix.
Thanks
**********************

Sub demo()
Dim i, Destinfile, Destinsheet, Destincolumn, wb1, wb2
Application.ScreenUpdating = False

startrow = 2

Range("A65536").Select
Selection.End(xlUp).Select
lastrow = ActiveCell.Row

Destinfile = InputBox("Please enter file name")
Destinsheet = InputBox("Enter Sheet name")
Destincolumn = InputBox("Enter column number for input")
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open(Destinfile)

For i = startrow To lastrow
inp = wb1.Sheets("Sheet1").Range("A" & i).Value
wb2.Sheets(Destinsheet).Activate

Cells.Find(What:=inp, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False).Activate

On Error Resume Next

rownum = ActiveCell.Row
outp = ActiveCell.Offset(0, Destincolumn - 1)
wb1.Sheets("Sheet1").Range("B" & i).Value = outp

Next i
wb2.Close False
Application.ScreenUpdating = True

End Sub

--

Dave Peterson