ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

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

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

FILE.rar (1.98 KB)

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-2-16 08:20 ½s¿è

¦^´_ 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

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-2-11 21:36 ½s¿è

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ register313 ©ó 2012-2-11 21:41 ½s¿è

¦^´_ 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

TOP

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

TOP

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

TOP

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

TOP

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

TOP

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

·PÁÂ

TOP

¦^´_ 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

TOP

        ÀR«ä¦Û¦b : ¨C¤ÑµL©Ò¨Æ¨Æ¡A¬O¤H¥Íªº®ø¶OªÌ¡A¿n·¥¡B¦³¥Î¤~¬O¤H¥Íªº³Ð³yªÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD