Sub test()
Dim prenom As Range, prenom2 As Range, addresse As Range
Rows("1:7").Clear
For Each prenom In Range([B9], [B9].End(xlDown))
Set addresse = prenom.Offset(0, 2)
With Workbooks("fich2.xls").Sheets("Feuil1")
Set prenom2 = .Cells.Find(prenom.Value, LookIn:=xlValues)
If prenom2 Is Nothing Then Set prenom2 = .Range("A65536").End(xlUp).Offset(1, 0)
prenom2 = prenom
prenom2.Offset(0, 2) = addresse
End With
Next
Workbooks("fich2.xls").Sheets("Feuil1").Activate
End Sub