Hsieh

¦^´_ 1# gn00487767

¤@Áä·d©w

1. Private Sub CommandButton2_Click()
   
2. Dim Ar()
   
3. With Sheets("¦W³æ")
   
4.    For Each a In .Range(.[E2], .[E2].End(xlDown)).SpecialCells(xlCellTypeConstants)
   
5.       ReDim Preserve Ar(s)
   
6.       n = Application.HLookup(a, Array(Array("²Ä¤@Ãþ", "²Ä¤GÃþ", "²Ä¤TÃþ", "²Ä¥|Ãþ", "²Ä¤­Ãþ"), Array(5, 3, 1, 0, 8)), 2, 0)
   
7.       y = DateAdd("m", n, a.Offset(, 1))
   
8.       Ar(s) = Array(y, n, IIf(Date > y, "¦³®Ä", "µL®Ä"))
   
9.       s = s + 1
   
10.     Next
    
11. End With
    
12. ay = Application.Transpose(Application.Transpose(Ar))
    
13. With Sheets("ÅçÃÒ")
    
14. .Range(.[B2], .[B2].End(xlDown)).ClearContents
    
15. .[B2].Resize(s, 1) = Application.Index(ay, , 1)
    
16. .Range(.[E2], .[E2].End(xlDown)).ClearContents
    
17. .[E2].Resize(s, 1) = Application.Index(ay, , 2)
    
18. End With
    
19. With Sheets("µ²ªG")
    
20. .Range(.[B2], .[B2].End(xlDown)).ClearContents
    
21. .[B2].Resize(s, 1) = Application.Index(ay, , 3)
    
22. End With
    
23. End Sub

½Æ»s¥N½X