Hsieh

¦^´_ 6# billchenfantasy

­Y¨Ì§Aªº½d¨Ò»¡©ú¬O­n²¾°£­«½Æ(­ì22¦C¸ê®Æ¡A¾ã²z«á¬°15¦C)
¬O¦³¥ý±N³Ì¥½2Äæ¦V«e²¾°Ê¡A¥u¬O¦³¦A«ì´_­ì»ª¦Ó¥H

1. Sub ex()
   
2. Dim Rng As Range, A As Range, C As Range
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. d("first") = Array("Mplan_no", "Mdate")    '·s¼ÐÃD
   
5. [A1].End(xlToRight).Offset(, -1).Resize(, 2).EntireColumn.Cut  '³Ì«á2Äæ°Å¤U
   
6. [C1].Insert  '¦bCÄæ´¡¤J°Å¤UªºÀx¦s®æ
   
7. For Each A In Range([A2], [A2].End(xlDown))
   
8. mystr = "": x = "": y = ""
   
9.   Set Rng = A.EntireRow.SpecialCells(xlCellTypeConstants, xlNumbers)  '¥H¤é´Á§@¬°°ò·Ç
   
10.   For Each C In Rng
    
11.      'mystr = IIf(mystr = "", C.Offset(, -1) & C, mystr & C.Offset(, -1) & C) '­Y­n±Æ°£­«½Æ«h¨Ï¥Î¦¹¬°¦r¨å¯Á¤Þ
    
12.      x = IIf(x = "", C.Offset(, -1), x & " " & C.Offset(, -1))
    
13.      y = IIf(y = "", C, y & " " & C)
    
14.   Next
    
15.   s = s + 1
    
16.   d(s) = Array(x, y)
    
17.   'd(mystr) = Array(x, y)  '­Y­n±Æ°£­«½Æ«h¨Ï¥Î¦¹¬°¦r¨å¯Á¤Þ
    
18. Next
    
19. [C:D].Cut [A1].End(xlToRight).Offset(, 1)  '±NC:DÄæ°Å¤U¶K¦^¸ê®Æªí³Ì¥½ºÝ
    
20. [C:D].Delete  'C:D°Å¤U«áÅܦ¨ªÅ¥ÕÄæ¡A©Ò¥H±N¨ä§R°£¡A¦^ÂЦ¨­ì¸ê®Æªí
    
21. [A1].End(xlDown).Offset(3).Resize(d.Count, 2) = Application.Transpose(Application.Transpose(d.items))
    
22. End Sub   

½Æ»s¥N½X

billchenfantasy

¦^´_ 5# billchenfantasy

¸É¥R¤@ÂI_¸ê®Æ¬O¤£»Ý²¾°£­«½ÆªºÁÂÁ±z

billchenfantasy

¦^´_ 3# Hsieh
·PÁ±zªºµªÂлP¨ó§U¡A­ì¨Ó¦³³o»ò¦h¼gªk¥i¥H°Ñ¦Ò
¦Ó¦b¹Á¸Õ¹B¥Î±zªº¤½¦¡®É¡Aµo²{¨Ã¥¼¥ý±N³Ì«á¨âÄd°Å¤U²¾¦Ü0-2¤§«e
¬O§_¬O¶·±N«á¤T¦æªº«ü¥O¶¶§Ç²¾°Ê©O?
·P®¦

billchenfantasy

¦^´_ 2# luhpro [/b
ÁÂÁ±zªºµªÂлP«ü¾É¡A½T¹ê·|¦]­pºâ¾É­P»~§Rªºª¬ªpµo¥Í¡A·PÁ±zªº²Ó¤ß¡C
±zªº¤½¦¡¦b¨Ï¥Î¹L«á¡A¸É»ô2-2¨º¤@Ä檺ª¬ªp¤U¥i¥H±N¸ê®Æ¾ã²z¦Ü±ý¹F¦¨ªº®æ¦¡¡C

¤U¸ü (61.85 KB)

2013-2-19 10:13

µM¦Ó¦b¨S¦³¸É»ôªºª¬ªp¤U¡A«h·|µo¥Í¥H¤Uª¬ªp

¤U¸ü (64.69 KB)
¦¨ªG¤G

2013-2-19 10:17

±zªº¤½¦¡§Ú·|¦n¦n¾Ç²ß¡A¦A¦¸·PÁÂ

Hsieh

¦^´_ 1# billchenfantasy
¬O§_¤]­n±Æ°£­«½Æ?

1. Sub ex()
   
2. Dim Rng As Range, A As Range, C As Range
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. d("first") = Array("Mplan_no", "Mdate")
   
5. [A1].End(xlToRight).Offset(, -1).Resize(, 2).EntireColumn.Cut
   
6. [C1].Insert
   
7. For Each A In Range([A2], [A2].End(xlDown))
   
8. mystr = "": x = "": y = ""
   
9.   Set Rng = A.EntireRow.SpecialCells(xlCellTypeConstants, xlNumbers)
   
10.   For Each C In Rng
    
11.      mystr = IIf(mystr = "", C.Offset(, -1) & C, mystr & C.Offset(, -1) & C)
    
12.      x = IIf(x = "", C.Offset(, -1), x & " " & C.Offset(, -1))
    
13.      y = IIf(y = "", C, y & " " & C)
    
14.   Next
    
15.   d(mystr) = Array(x, y)
    
16. Next
    
17. [C:D].Cut [A1].End(xlToRight).Offset(, 1)
    
18. [C:D].Delete
    
19. [A1].End(xlDown).Offset(3).Resize(d.Count, 2) = Application.Transpose(Application.Transpose(d.items))
    
20. End Sub

½Æ»s¥N½X

luhpro

§e¤W ¦]¸ê®Æµ§¼Æ¸û¦h ½Ð¦U¦ì«e½ú½ç±Ð ·P®¦
billchenfantasy µoªí©ó 2013-2-18 14:16

Æ[¬Ý§AÁ|¨Òªºµ²ªG»P¤W¤è­ì½Z¤º®e¦ü¥G¦³³¡¤À¦a¤èµLªk¹ïÀ³,¨Ò¦p :
M12 ÁöµM¦³¹ïÀ³ 2002/9/24
¦ý§ä¤£¨ì M63 (0-0 2-2) ¸Ìªº 2-2 (M64 ¬O 1974/8/22 2002/9/24)
¬G°²³]§A­ì½Z¤¤ Plan_Dat_5 »P Plan_Dat_6 (§Y L Äæ»P M Ä涡)¤¤¶¡¦³¤@Äæ³Q»~§R¤F.
©³¤Uªºµ{¦¡¬O¥H¦¹°²³]¬°°ò¦°µ¥X¨Óªº.

1. Sub ex()
   
2.   Dim iCols%, iCol%
   
3.   Dim lRows As Long, lRow As Long
   
4.   
   
5.   iCols = Cells(1, Columns.Count).End(xlToLeft).Column
   
6.   Range(Columns(iCols - 1), Columns(iCols)).Cut
   
7.   Columns(3).Insert xlShiftToRight
   
8.   lRows = Cells(Rows.Count, 1).End(xlUp).Row
   
9.   
   
10.   For lRow = 2 To lRows
    
11.     For iCol = 3 To iCols Step 2
    
12.       If Cells(lRow, iCol) <> "" Then
    
13.         Cells(lRow, 1) = Cells(lRow, 1) & " " & Cells(lRow, iCol)
    
14.         Cells(lRow, iCol) = ""
    
15.         Cells(lRow, 2) = Cells(lRow, 2) & " " & Cells(lRow, iCol + 1)
    
16.         Cells(lRow, iCol + 1) = ""
    
17.       End If
    
18.     Next iCol
    
19.   Next lRow
    
20.   End Sub

½Æ»s¥N½X