Hsieh

¦^´_ 1# luke

1. Sub ex()
   
2. Dim ay(2, 11)
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. With sheet2
   
5. For Each a In .Range(.[A2], .[A2].End(xlDown))
   
6.    d(a.Value) = a.Offset(, 1).Value
   
7. Next
   
8. End With
   
9. With sheet1
   
10. .[B1:k1].Copy .[B14]: r = 15
    
11. For Each a In .Range(.[A2], .[A2].End(xlDown))
    
12.   ar = a.Resize(, 11).Value
    
13.   For i = 1 To 11
    
14.     ay(0, i - 1) = ar(1, i)
    
15.     ay(1, i - 1) = d(ar(1, i))
    
16.   Next
    
17.   .Cells(r, 1).Resize(2, 11) = ay
    
18.   r = r + 3
    
19.   Erase ay
    
20. Next
    
21. End With
    
22. End Sub

½Æ»s¥N½X