Hsieh

¦^´_ 2# ounmaxz


    ¬Ý¤£¥X³æ¦ì¦p¦ó¹ïÀ³¥ý¥H"³æ¦ì"¥Nªí

1. Sub nn()
   
2. Dim Rng As Range, A As Range, Ar(), Ay()
   
3. With Sheet2
   
4. Set Rng = .Range(.[N5], .[N7].End(xlUp)) '³]¸m¤ñ¹ïªº¼Ð·Ç°Ï°ì
   
5. With Sheet1
   
6. ReDim Preserve Ay(0)
   
7. Ay(0) = .[A1].Resize(, 14).Value
   
8. For Each A In .Range(.[A1], .[A65536].End(xlUp)) '¦bsheet1ªºdÄæ¸ê®Æ´`Àô
   
9.     If Not Rng.Find(A, lookat:=xlWhole) Is Nothing And A.Offset(, 13) = "¥[¯Z" Then '¦pªG¼Ð·Ç°Ï§ä¨ìdÄ檺­È
   
10.        ReDim Preserve Ar(s)
    
11.        ReDim Preserve Ay(s + 1)
    
12.        Ar(s) = Array("³æ¦ì", A.Value, A.Offset(, 1).Value, "", A.Offset(, 2).Value, Format(A.Offset(, 3).Value, "hh:mm"), Format(A.Offset(, 4).Value, "hh:mm"), A.Offset(, 5).Value)
    
13.        Ay(s + 1) = A.Resize(, 14).Value
    
14.        s = s + 1
    
15.     End If
    
16. Next
    
17. End With
    
18. .[A5:I20] = ""
    
19. Sheet3.Cells = "" '²MªÅSheet3¤º®e
    
20. If s > 0 Then .[A5].Resize(s, 8) = Application.Transpose(Application.Transpose(Ar)): _
    
21. Sheet3.[A1].Resize(s + 1, 14) = Application.Transpose(Application.Transpose(Ay)) '§âSheet1²Å¦Xªº¦C½Æ»s¨ìSheet3ªºA1
    
22. End With
    
23. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 4# ounmaxz

1. Sub nn()
   
2. Dim Rng As Range, A As Range, Ar(), Ay()
   
3. With Sheet2
   
4. Ut = .[P5].Value
   
5. Set Rng = .Range(.[N5], .[N65536].End(xlUp)) '³]¸m¤ñ¹ïªº¼Ð·Ç°Ï°ì
   
6. With Sheet1
   
7. ReDim Preserve Ay(0)
   
8. Ay(0) = .[A1].Resize(, 14).Value
   
9. For Each A In .Range(.[A1], .[A65536].End(xlUp)) '¦bsheet1ªºdÄæ¸ê®Æ´`Àô
   
10.     If Not Rng.Find(A, lookat:=xlWhole) Is Nothing And A.Offset(, 13) = "¥[¯Z" Then '¦pªG¼Ð·Ç°Ï§ä¨ìdÄ檺­È
    
11.        ReDim Preserve Ar(s)
    
12.        ReDim Preserve Ay(s + 1)
    
13.        Ar(s) = Array(Ut, A.Value, A.Offset(, 1).Value, "", A.Offset(, 2).Value, Format(A.Offset(, 3).Value, "hh:mm"), Format(A.Offset(, 4).Value, "hh:mm"), A.Offset(, 5).Value)
    
14.        Ay(s + 1) = A.Resize(, 14).Value
    
15.        s = s + 1
    
16.     End If
    
17. Next
    
18. End With
    
19. .[A5:I20] = ""
    
20. Sheet3.Cells = "" '²MªÅSheet3¤º®e
    
21. If s > 0 Then .[A5].Resize(s, 8) = Application.Transpose(Application.Transpose(Ar)): _
    
22. Sheet3.[A1].Resize(s + 1, 14) = Application.Transpose(Application.Transpose(Ay)) '§âSheet1²Å¦Xªº¦C½Æ»s¨ìSheet3ªºA1
    
23. End With
    
24. End Sub
    
25. 
    
26. Sub yy() '¶ñº¡¤é´Á
    
27. y = InputBox("¿é¤J¦è¤¸¦~«×", , 2010)
    
28. With Sheet1
    
29. Ar = .[B2:C17].Value
    
30. r = 2
    
31. For i = DateValue(y & "/1/1") To DateValue(y & "/12/31")
    
32.   .Cells(r, 1).Resize(16, 1) = i
    
33.   .Cells(r, 2).Resize(16, 2) = Ar
    
34.   r = r + 16
    
35. Next
    
36. End With
    
37. End Sub

½Æ»s¥N½X