register313

¦^´_ 1# luke

½Ð¤W¶ÇEXCELÀ£ÁYÀÉ,¨Ã»¡©ú©Ò»Ý¤§¥\¯à­n¨D

register313

¦^´_ 3# luke

1. Sub XX()
   
2. Set Rng = Union([A15:K15], [A22:K22], [A29:K29], [A36:K36])
   
3. For Each R In Rng
   
4.     Set s = Sheets("Sheet2").[A:A].Find(R, , , xlWhole)
   
5.     If Not s Is Nothing Then
   
6.        R.Resize(6, 1) = Application.Transpose(s.Resize(1, 6))
   
7.     End If
   
8. Next
   
9. End Sub

½Æ»s¥N½X

register313

¦^´_ 5# Hsieh

­YÀx¦s®æ¬°¼Æ¦r¦ý¨Ã«D¦r¨å¸ÌªºÃöÁä¦r,¸Ó¦p¦ó¨¾¿ù

1. Sub nn()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. With sheet2
   
4.    For Each a In .Range(.[A1], .[A1].End(xlDown))
   
5.       d(a.Value) = a.Offset(, 1).Resize(, 5).Value
   
6.    Next
   
7. End With
   
8. With sheet1
   
9. For Each a In .UsedRange.SpecialCells(xlCellTypeConstants, xlNumbers)
   
10.    If d.Exists(a.Value) Then
    
11.       a.Offset(1).Resize(UBound(d(a.Value), 2), 1) = Application.Transpose(d(a.Value))
    
12.    End If
    
13. Next
    
14. End With
    
15. End Sub

½Æ»s¥N½X