[µo°Ý] ½Ð±Ðvba ---- sheet1Åã¥Ü¦Usheet³¡¤ÀÄæ¦ì¸ê°T

smallpoper

½Ð°Ý¤@¤U , ¦pªG§Ú¦³n­Ósheet¦Ósheet(2)~sheets(n)ªº³¡¤ÀÄæ¦ì¸ê°T­nÅã¥Ü¦bsheet(1)...(¸ê®Æ¦pªþ¥ó)
»yªk­n«ç»ò¼g
smaple.rar (16.3 KB)

¤U¸ü¦¸¼Æ: 8

2014-4-13 10:53

GBKEE

¦^´_ 1# smallpoper

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Ar(4) '°}¦Cªº¯Á¤Þ­È 0-4
   
4.     With Sheets("Summary").[A8:J30]
   
5.         .Cells = ""         '½d³òªºCell
   
6.         For i = 2 To Sheets.Count  '¬¡­¶Ã¯¤WSummary³o¤u§@±Æ¦b²Ä¤@­Ó,¨Ì§ÇŪ¨ú«á­±ªº¤u§@ªí
   
7.             With Sheets(i)
   
8.                 Ar = Array(.[B4], Split(.[B8], "#")(0), .[H5], .[E4], UBound(Split(.[E4], ",")) + 1)
   
9.                 '»Ý­nªº¸ê®Æ¸m©ó °}¦C¤¤
   
10.             End With
    
11.             .Cells(i - 1, 1).Resize(, 5) = Ar  '[A8:J30].Cells(¦C¸¹1, Ä渹)
    
12.             'Resize(¦C¼Æ,Äæ¼Æ) (±N­ì¥»½d³òÂX¤j )
    
13.         Next
    
14.     End With
    
15. End Sub

½Æ»s¥N½X

smallpoper

¦^´_ 2# GBKEE
½Ð°Ý³o¬q¥N½X¬O¤°»ò·N«ä
Ar = Array(.[B4], Split(.[B8], "#")(0), .[H5], .[E4], UBound(Split(.[E4], ",")) + 1)

GBKEE

¦^´_ 3# smallpoper

1. Ar = Array(.[B4], Split(.[B8], "#")(0), .[H5], .[E4], UBound(Split(.[E4], ",")) + 1)
   
2. 
   
3. 'Split ¨ç¼Æ ¶Ç¦^¤@­Ó³¯¦C¯Á¤Þ±q¹s¶}©lªº¤@ºû°}¦C¡A¥¦¥]§t«ü©w¼Æ¥Øªº¤l¦r¦ê¡C
   
4. 'Split(.[B8], "#") ±N.[B8]ªº¦r¦ê¥H"#"³o¦r¤¸ ¤À³Î¬°¤@°}¦C
   
5. 
   
6. 'UBound ¨ç¼Æ ¶Ç¦^ Long­È¡Aªí¥Ü«ü©w°}¦C¬Yºû³Ì¤j¥i¨Ï¥Îªº°}¦C¯Á¤Þ¡C
   
7. 'Split(.[E4], ",")         -> ¶Ç¦^¤@°}¦C
   
8. 'UBound(Split(.[E4], ",")) -> ¶Ç¦^³o°}¦C¥i¨Ï¥Îªº°}¦C¤¸¯À¯Á¤Þ
   
9. Option Base »yªk Option Base {0 | 1}
   
10. Option Base 0 '³¯­z¦¡¦b¼Ò²Õ¼h¦¸¤¤¥Î¨Ó«Å§i°}¦C¤¸¯À¯Á¤Þªº¹w³]¤U­­¡C
    
11. '½Ðª`·N ­Y¹w³]ªº¤U­­­È¬° 0¡A«h Option Base ³¯­z¦¡¬O¤£»Ý­nªº
    
12. '©Ò¥HUBound(Split(.[E4], ",")) + 1 ¤~¬O°}¦C¯u¥¿ªº¤¸¯À­Ó¼Æ

½Æ»s¥N½X

smallpoper

¦^´_ 4# GBKEE

ÁÂÁ±zªº¸Ô¸Ñ , ÁöµM²{¶¥¬q¥u¬O¤JªùÁÙ¤£¤ÓÀ´ , ¨Ó¥h¬ã¨s¬ã¨s¾ã¬q¥N½X . ¦A¦¸·PÁ±z:)