Hi:

I have two workbooks 1.xls and 2.xls. Both workbooks have in column A
a list of "jobs" but not in the same order, such as:

1.xls

job 444
job 888
job 222
job 555


2.xls

job 222
job 555
job 888
job 444


What I need is to compare 1.'xls to 2.xls and if it finds the job i
2.xls then copy a cell from 2.xls but 2 columns down from the job to
cell in the same row as job in 1.xls but 9 columns down.

Any help would be appreciated

--
halem
-----------------------------------------------------------------------
halem2's Profile: http://www.excelforum.com/member.php...nfo&userid=993
View this thread: http://www.excelforum.com/showthread.php?threadid=27221

First, does 2 columns down mean 2 columns to the right?
And 9 columns down means 9 columns to the right???

If yes, then how about this:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng1 As Range
Dim myRng2 As Range
Dim res As Variant

With Workbooks("book1.xls").Worksheets("sheet1")
Set myRng1 = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Workbooks("book2.xls").Worksheets("sheet2")
Set myRng2 = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng1.Cells
res = Application.Match(myCell.Value, myRng2, 0)
If IsError(res) Then
'not found
Else
myCell.Offset(0, 9).Value _
= myRng2(res).Offset(0, 2)
End If
Next myCell

End Sub

Adjust the workbook names and worksheet names to match your situation.


halem2 wrote:

Hi:

I have two workbooks 1.xls and 2.xls. Both workbooks have in column A,
a list of "jobs" but not in the same order, such as:

1.xls

job 444
job 888
job 222
job 555

2.xls

job 222
job 555
job 888
job 444

What I need is to compare 1.'xls to 2.xls and if it finds the job in
2.xls then copy a cell from 2.xls but 2 columns down from the job to a
cell in the same row as job in 1.xls but 9 columns down.

Any help would be appreciated.

--
halem2
------------------------------------------------------------------------
halem2's Profile: http://www.excelforum.com/member.php...fo&userid=9930
View this thread: http://www.excelforum.com/showthread...hreadid=272215

--

Dave Peterson