Hi Howard,

Am Mon, 7 Oct 2013 10:09:08 -0700 (PDT) schrieb Howard:

Looks up a number from sheet 1, Column A in Sheet 2 Column E, and posts offsets from both the left and right of that Col E number back to Column A.

Once the post has been completed the worksheet/book freezes and offers a not responding massage. Restart of Excel is required.

I hope I understood your problem.
Try:

Sub ListNewPN()

Dim rngPN As Range
Dim c As Range
Dim ws1Part_Num As Range
Dim ws2From_Item As Range
Dim firstaddress As String
Dim LRow1 As Long
Dim LRow2 As Long

With Sheets("Sheet1")
LRow1 = .Cells(.Rows.Count, 1).End(xlUp).Row
Set ws1Part_Num = .Range("A1:A" & LRow1)
End With

With Sheets("Sheet2")
LRow2 = .Cells(.Rows.Count, 5).End(xlUp).Row
Set ws2From_Item = .Range("E1:E" & LRow2)


For Each c In ws1Part_Num
Set rngPN = ws2From_Item.Find(c, LookIn:=xlValues, _
lookat:=xlWhole)

If Not rngPN Is Nothing Then
Do
firstaddress = c.Address
c.Offset(0, 1) = rngPN.Offset(, -1)
c.Offset(0, 2) = rngPN.Offset(, 1)
Set rngPN = ws2From_Item.FindNext(rngPN)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
Next c
End With
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2