Hi Howard,

Am Tue, 30 Jul 2013 23:07:40 -0700 (PDT) schrieb Howard:

I need it to find all occurrences of TJ and return the 122; (with semi-colon) in col D

in column D of the first occurence of TJ you will get all values with
semicolon:

Sub Auto_Mate()
Dim lngLstRow As Long
Dim shDes As Range
Dim shDesCol As Range
Dim firstAddress As String

lngLstRow = Sheets("Sheet1").UsedRange.Rows.Count
With Sheets("Sheet1").Range("C1:C" & lngLstRow)
Set shDesCol = .Find(what:="TJ", LookIn:=xlValues, Lookat:=xlPart)
If Not shDesCol Is Nothing Then
firstAddress = shDesCol.Address
Do
Range(firstAddress).Offset(, 1) = _
Range(firstAddress).Offset(, 1) & shDesCol.Offset(, -1) & "; "
Set shDesCol = .FindNext(shDesCol)
Loop While Not shDesCol Is Nothing And _
shDesCol.Address < firstAddress
End If
End With
End Sub


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