Hsieh

¦^´_ 1# luke

1. Sub nn()
   
2. With sheet2
   
3. Set Rng = .Range(.[A1], .[A1].End(xlDown))
   
4. k = 11
   
5. For Each a In sheet1.[B1:B5]
   
6. mystr = "SUMPRODUCT((left(sheet2!" & Rng.Address & ",2)=""" & a.Offset(, -1) & """)*1)"
   
7. Set c = .Columns("A:A").Find(a.Offset(, -1), after:=.[A65536], lookat:=xlPart)
   
8. r = Evaluate(mystr)
   
9. For i = 1 To a
   
10.    c.Resize(r, 10).Copy sheet1.Cells(k, 1)
    
11.    k = k + r
    
12. Next
    
13. Next
    
14. End With
    
15. End Sub

½Æ»s¥N½X