Hsieh

¦^´_ 1# s7659109

VBA»²§U¨Óªº®e©ö

1. Sub ex()
   
2. Dim Ay(2), Ary()
   
3. Set dic = CreateObject("Scripting.Dictionary")
   
4. With Sheets("Á`ªí")
   
5.     For Each a In .Range(.[B3], [B3].End(xlDown))
   
6.       If IsEmpty(dic(a.Value)) Then
   
7.          ar = Array(a.Value, "", "¶i³f", "", "", "¥X³f", "", "")
   
8.          Ay(0) = ar
   
9.          Ay(1) = .[A2:H2].Value
   
10.          Ay(2) = a.Offset(, -1).Resize(, 8).Value
    
11.          dic(a.Value) = Ay
    
12.          Else
    
13.          Ary = dic(a.Value)
    
14.          ReDim Preserve Ary(UBound(Ary) + 1)
    
15.          Ary(UBound(Ary)) = a.Offset(, -1).Resize(, 8).Value
    
16.          dic(a.Value) = Ary
    
17.         End If
    
18.       Next
    
19. End With
    
20. For Each ky In dic.keys
    
21.    With Sheets(ky)
    
22.      .[A1].Resize(UBound(dic(ky)) + 1, 8) = Application.Transpose(Application.Transpose(dic(ky)))
    
23.      .Cells(.Rows.Count, 1).End(xlUp).Offset(1) = "¦X­p"
    
24.      .Cells(.Rows.Count, 8).End(xlUp).Offset(1) = "=SUM(R1C:R[-1]C)"
    
25.    End With
    
26. Next
    
27. End Sub

½Æ»s¥N½X