Private Sub CommandButton1_Click()
Dim cel As Range, ref As Range, n As Byte, tablo
For Each cel In Range("D2", Range("D65536").End(xlUp))
Set ref = Sheets("Sheet2").Columns("A").Find(cel, LookIn:=xlValues, LookAt:=xlWhole)
If Not ref Is Nothing Then
n = ref.MergeArea.Rows.Count
tablo = Application.Transpose(ref.Offset(, 1).Resize(n))
cel.Offset(, 1).Resize(, n) = tablo
End If
Next
End Sub