[¨D§U]¦p¦ó¿z¿ï¸û¨Î

baa168

Dear All:
       1.±NA°Ï¨C­Ó¤ë³Ì«á¤@¶g´£¥X¦p¶À¦â¼Ð¥Ü³¡¥÷
           2.±N¤é´Á¥h¦~¥÷Åã¥Ü

¥H¤W
½Ð¨ó§U
Thanks.

GBKEE

¦^´_ 1# baa168
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim xR As Integer, xMonth As Integer, Ar(), xAr As Integer
   
4.     xR = 4                                           '²Ä4¦C
   
5.     ReDim Ar(1, xAr)                                 '­«·s«Å§i  AR(0 TO 1,0) ¤Gºû°}¦C
   
6.     Ar(0, xAr) = "¤é´Á"                              'Ar(0, 0) = "¤é´Á"
   
7.     Ar(1, xAr) = "¤º®e"                              'Ar(1, 0) = "¤º®e"
   
8.     With ActiveSheet
   
9.         xMonth = Month(.Cells(xR, "A"))               '¨ú±o A4¤ë¥÷
   
10.         Do While .Cells(xR, "A") <> ""                '°õ¦æ°j°é±ø¥ó  ¤£¬OªÅ¥ÕªºÀx¦s®æ
    
11.             If Month(.Cells(xR + 1, "A")) <> xMonth Or .Cells(xR + 1, "A") = "" Then
    
12.                 '¤U¤@¦Cªº¤ë¥÷<>³o¤@¦Cªº¤ë¥÷  ©Î¬O  ¤U¤@¦C¬OªÅ¥Õ
    
13.                  xMonth = Month(.Cells(xR + 1, "A"))    '§ó§ï¤ë¥÷ªº¼Æ­È
    
14.                 xAr = xAr + 1                          '¤Gºû°}¦C ªº²Ä¤Gºû¦A¥[¤@­Ó¤¸¯À
    
15.                 ReDim Preserve Ar(1, xAr)              '­«·s«Å§i  AR(0 TO 1,xAr) ¤Gºû°}¦C
    
16.                 'Preserve ¿ï¾Ü©Ê¤Þ¼Æ¡C·í§ïÅܭ즳°}¦C³Ì«á¤@ºûªº¤j¤p®É¡A¤´µM«O¦³­ì¨Óªº¸ê®ÆªºÃöÁä¦r¡C
    
17.                 Ar(0, xAr) = .Cells(xR, "A")           'Ar(0, xAr) = "¤é´Á"
    
18.                 Ar(1, xAr) = .Cells(xR, "B")           'Ar(0, xAr) = "¤º®e"
    
19.             End If
    
20.             xR = xR + 1                                 '¦A©¹¤U¤@¦C
    
21.         Loop
    
22.         .Range("G3").CurrentRegion = "" 'CurrentRegion Äݩʥثe°Ï°ì¬O«ü¥H¥ô·NªÅ¥Õ¦C¤ÎªÅ¥ÕÄ檺²Õ¦X¬°Ãä¬Éªº½d³ò¡C°ßŪ¡C
    
23.         .Range("G3").Resize(xAr + 1, 2) = Application.Transpose(Ar)   '¹B¥Î¤u§@ªí¨ç¼Æ Transpose (Âà¸m) Ar °}¦C
    
24.         .Range("G3").Resize(xAr + 1).NumberFormatLocal = "m/d;@"      ' ¨î­qÀx¦s®æ ¤é´Á®æ¦¡
    
25.     End With
    
26. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 1# baa168

1. Sub nn()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. d("¤é´Á") = Array("¤é´Á", "¤º®e")
   
4. For Each a In Range("A4", [A65536].End(xlUp))
   
5. mystr = Format(a, "yyyymm")
   
6.   d(mystr) = Array(a.Value, a.Offset(, 1).Value)
   
7. Next
   
8. [G:H] = ""
   
9. [G3].Resize(d.Count, 2) = Application.Transpose(Application.Transpose(d.items))
   
10. End Sub

½Æ»s¥N½X

register313

¦^´_ 1# baa168

1. Sub AA()
   
2. Columns("G:H") = ""
   
3. [G3] = "¤é´Á": [H3] = "¤º®e"
   
4. T = 4
   
5. For R = 4 To Range("A65536").End(xlUp).Row
   
6.   If (Month(Cells(R, "A")) <> Month(Cells(R + 1, "A"))) Or (R = Range("A65536").End(xlUp).Row) Then
   
7.      Cells(T, "G") = Right(Cells(R, "A"), Len(Cells(R, "A")) - 5)
   
8.      Cells(T, "G").NumberFormatLocal = "m/d;@"
   
9.      Cells(T, "H") = Cells(R, "B")
   
10.      T = T + 1
    
11.   End If
    
12. Next R
    
13. End Sub

½Æ»s¥N½X

baa168

·PÁ¦U¦ì¤j¤j¤j¤Oªº¨ó§U...baa¥ý®ø¤Æ¤@¤U...

baa168

Hsieh¤j¸òregister313¤j§¡¥X²{¿ù»~°T®§..¤£ª¾¬O¤£¬Obaa·d¿ù¤F.....

baa168

GBKEE¤j¤£ª¾¬O§_¥i¥H½Ð±z¸ÑÄÀ¤@¤Uµ{¦¡¤º®e...¬ã¨s¤F¨â¤ÑÁÙ¬O¨S¯à¤F¸Ñ...¬Ý¨Ó¬O¶W¹L¤Fbaa¹ïVBAªºµ{«×¤F...·PÁÂ...

baa168

·PÁÂGBKEEªº¨ó§U...®ø¤Æ¤¤...

baa168

½Ð±Ð¦U¦ì¥ý¶i
­Y­n±N©Ò¿z¿ï¸ê®ÆÅã¥Ü¦ÜSheet2¡A¸Ó¦p¦ó¶i¦æ

·PÁÂ

register313

¦^´_ 9# baa168

1. Sub AA()
   
2. Sheets("Sheet2").Columns("A:B") = ""
   
3. Sheets("Sheet2").[A3] = "¤é´Á": Sheets("Sheet2").[B3] = "¤º®e"
   
4. T = 4
   
5. With Sheets("Sheet1")
   
6. For R = 4 To .Range("A65536").End(xlUp).Row
   
7.   If (Month(.Cells(R, "A")) <> Month(.Cells(R + 1, "A"))) Or (R = .Range("A65536").End(xlUp).Row) Then
   
8.      Sheets("Sheet2").Cells(T, "A") = Right(.Cells(R, "A"), Len(.Cells(R, "A")) - 5)
   
9.      Sheets("Sheet2").Cells(T, "A").NumberFormatLocal = "m/d;@"
   
10.      Sheets("Sheet2").Cells(T, "B") = .Cells(R, "B")
    
11.      T = T + 1
    
12.   End If
    
13. Next R
    
14. End With
    
15. End Sub

½Æ»s¥N½X