Two workbooks

  • Thread starter Thread starter halem2
  • Start date Start date
H

halem2

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
 
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.
 
Back
Top