Comparing/Moving data between 2 sheets

[thegoldphish]

I have a workbook with 2 sheets.

Sheet 1 has numerous rows with either a number or nothing in row H.
Example:

H
55655

45666
456456

44455
55336

34553

Sheet 2 has numerous rows with this same number in row E (not the same row numbers as sheet 1).
Example:

E
34553
45666
55655
44455
55336
456456





Now here is the hard part. I want to loop through all the numbers on sheet 1 to find which row they are on on sheet 2. Then I want to copy the cell 5 spaces to the right of wherever that number is on sheet 2 and paste it to the right of the place the number is on sheet 1.
Example:

Sheet 1                                Sheet 2

  H    I                           E   F  G  H  I   J
55655  Num <--paste here        55655  x  x  x  x  Num <--copy this

All of the numbers in the H column(sheet1) will be on the E column(sheet2). Is this possible?

 

[VHS01]

thegoldphish,

First, sorry about the code format....
I need to re-find the html editor from this site again.

The following code will work.
It actually could be cleaner, but I exaggerated it to let you see the whole process.

Sub CompareSheets()
Dim a As Integer, b As Integer, c As String
For a = 1 To Sheets("Sheet1").Range("H65536").End(xlUp).Row
For b = 1 To Sheets("Sheet2").Range("E65536").End(xlUp).Row
If Sheets("Sheet1").Range("H" & a).Value = Sheets("Sheet2").Range("E" & b).Value Then
Sheets("Sheet1").Range("I" & a).Value = Sheets("Sheet2").Range("J" & b).Value
End If
Next b
Next a
End Sub

Gary