Hi Howard,

Am Thu, 25 Jun 2015 08:10:10 +0200 schrieb Claus Busch:

if Nme is only once in sheet Input but can occur more than once in
Output where should the data go?
If you overwrite the existing data you get only the last match.

try it this way. If Nme is found more than once new rows are created in
Input. After running the macro you can sort Input to put the same Nme
together:

Sub Nme_Find()
Dim rngFound As Range
Dim Nme As Range
Dim OneRng As Range
Dim FirstAddress As String

Set OneRng = Sheets("Input").Range("A2:A" & Cells(Rows.Count,
"A").End(xlUp).Row)

For Each Nme In OneRng
Set rngFound = Sheets("Output").Range("A:A").Find(What:=Nme.Value , _
LookIn:=xlValues, _
LookAt:=xlWhole)

If Not rngFound Is Nothing Then
FirstAddress = rngFound.Address
Do
If Application.CountA(Nme.Offset(, 1).Resize(1, 41)) = 0 Then
rngFound.Offset(, 1).Resize(1, 41).Copy Nme.Offset(, 1)
Else
rngFound.Resize(1, 41).Copy _
Sheets("Input").Cells(Rows.Count, 1).End(xlUp)(2)
End If
Set rngFound = Sheets("Output").Range("A:A").FindNext(rngFound)
Loop While Not rngFound Is Nothing And rngFound.Address <
FirstAddress
End If
Next
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional