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

[µo°Ý] ¦p¦ó±NSheet1¤£³sÄòÄæ¦ìÀx¦s¨ìSheet2

[µo°Ý] ¦p¦ó±NSheet1¤£³sÄòÄæ¦ìÀx¦s¨ìSheet2

¦U¦ì«e½ú±z¦n

¤p§Ì¦bExcel¶i¶¥¥\¯àªº³¡¥÷¬O·s¤â,
°Ñ¦Ò¤§«e«e½úªº»¡©ú©M±Ô­z,
¼g¥X³o­Ósample,
³o­Ó¦³¨â­Ósheet,
§Ú­n±Nsheet1ªº¸ê®Æ¦b«ösaveÁä®É½Æ»s¨ìsheet2·í°µ¸ê®Æ®w,
¦ý¬O¤£ª¾¹D¦p¦ó±N©m¦W,¾Ç¸¹©M¹q¸Ü¦p¦ó¦bsheet2§¹¾ã§e²{,
¤£µM³£¦bsheet2¥u¦³²Ä¤@µ§¦³©m¦W,¾Ç¸¹©M¹q¸Ü,
²Ä¤Gµ§¤§«á¦b©m¦W,¾Ç¸¹©M¹q¸Ü¬OªÅªº.
¥i¥H³Â·Ð«e½úÀ°¦£¸Ñ¨M¤p§Ìªº°ÝÃD¶Ü?
«D±`·PÁÂ. sample.rar (8.7 KB)
Steven

¦^´_ 1# steven_lkk

§Ú­è­è´ú¸Õ¹L¬Ý¤£¥X¦³§A»¡ªº±¡§Î©O, (°õ¦æµ²ªG¦p¤U¹Ï)
¤£ª¾§A»¡ªº»P§Úªº²z¸Ñ¬O§_¤£¦P?

TOP

  1. Sub Ex()
  2. With Sheet1
  3. For Each a In .Range(.[A4], .[A65536].End(xlUp))
  4. ar = Array(a, .[A2], .[C2], .[E2], a.Offset(, 1), a.Offset(, 1), a.Offset(, 2), a.Offset(, 3), a.Offset(, 4))
  5. Sheet2.[A65536].End(xlUp).Offset(1, 0).Resize(, 8) = ar
  6. Next
  7. End With
  8. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦U¦ì«e½ú±z¦n

·PÁ¦U¦ìªºÀ°¦£,
Hsieh«e½ú¸Ñ¨M¤F¤p§Ìªº°ÝÃD,
¤p§Ì¬ãŪHsieh«e½úªº»yªk«á,
¦ý¹ê¦b¤£À´¤U­±ªº»yªk,
ar = Array(a, .[A2], .[C2], .[E2], a.Offset(, 1), a.Offset(, 1), a.Offset(, 2), a.Offset(, 3), a.Offset(, 4))
Sheet2.[A65536].End(xlUp).Offset(1, 0).Resize(, 8) = ar
¬ßHsieh«e½ú¥i¥H¦A±Ð¾É¤p§Ì¤@µf.
«D±`·PÁÂ.
Steven

TOP

¥»©«³Ì«á¥Ñ steven_lkk ©ó 2010-9-6 22:43 ½s¿è

¦U¦ì«e½ú±z¦n

¦pªG¨¾¤î¨Ï¥ÎªÌ½s¿èsheet2,
§Ú¤§«e¬O¥ÎÂêÀx¦s®æªº¤è¦¡,
¦ý¬Oµo²{·|³y¦¨µLªk½Æ»s.
«e½ú¥i¥H¦A¦¸±Ð¾É¤p§Ì¤@µf.
«D±`·PÁÂ.
Steven

TOP

¦^´_ 5# steven_lkk


ar = Array(a, .[A2], .[C2], .[E2], a.Offset(, 1), a.Offset(, 1), a.Offset(, 2), a.Offset(, 3), a.Offset(, 4)) '«ü©war°}¦CÅܼƤº®e
Sheet2.[A65536].End(xlUp).Offset(1, 0).Resize(, 8) = ar '±Nar°}¦C¼g¤JSheet2ªºAÄæ³Ì¤U­±¸ê®Æªº¤U¤@­ÓªÅ®æÂX®i¦¨1¦C8Äæ½d³ò
¦Ü©ó«OÅ@°ÝÃD
µ{¦¡¶}©l¥[¤J
Sheet2.Unprotect "password"  'password¬O§Aªº¤u§@ªí«OÅ@±K½X
µ{¦¡µ²§ô¥[¤J
Sheet2.Protect "password"
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

«e½ú±z¦n
½Ð°Ý¤@¤U,ar°}¦CÅܼƤº®e¬O§_¹³³o­Ó¹Ï¤@¼Ëªº¹ïÀ³¦ì¸m©O?

¨º³Ì²Ä¤@­Óªºa, (ar = Array(a, .....)¥Nªí¤°»ò©O?
¬°¤°»ò¤S·|¦h¤@­Óa.Offset(, 1)©O?
«D±`·PÁ±zªºÀ°¦£.
Steven

TOP

«e½ú±z¦n

¤p§Ì¨Ì·Ó¤W­Ósample·í½d¥»¨Ó½m²ß»s§@¤@­Ó³ø»ù³æ,
µ²ªG«ö¤Fsave«á¦bListùØ¥X²{´X­Ó°ÝÃD,
1.¤é´Á¤£¬O§Ú­ì¨Óªº¤º®e
2.§Ú¥u·Q²{¦³ªº¨âµ§¸ê®Æ½Æ»s¨ìlistùØ,µ²ªGlist¥X²{­«ÂЦh¾lªº¸ê®Æ

§Ú¸Ó«ç»ò­×¥¿©O??

¥t¥~¦³¤@­Ó°ÝÃD,
¦pªG³æ¸¹ªº²Õ¦¨¬O­^¤å¦r¥À+¤é´Á+¨â¦ì¼Æªº¬y¤ô¸¹,
§Æ±æ¯à¦Û°Ê²£¥Í³æ¸¹,
¤£ª¾¸Ó¦p¦ó¼g³o¬q©O?
¬ß«e½ú½ç±Ð.
«D±`·PÁÂ
sample2.rar (12.67 KB)
Steven

TOP

¦U¦ì«e½ú±z¦n~

¤p§Ì¥¢¸ÕµÛ¹Á¸Õ­×¥¿°ÝÃD,¦ý¬O´X¤é¤U¨Ó¥¼­×ªº¥¿ªG,
¬ß¦U¦ì«e½ú½ç±Ð.

«D±`·PÁÂ
Steven

TOP

  1. Private Sub CommandButton1_Click()
  2. With Sheet3
  3. For Each a In .Range(.[B13], .[B26].End(xlUp))
  4. i = i + 1
  5. ar = Array(.[f7].Text, "s" & .[f7].Text & "-" & Format(i, "00"), .[C6], .[C7], .[C8], .[C9], .[C10], .[F8], a, a.Offset(, 1), a.Offset(, 2), a.Offset(, 3), a.Offset(, 4))
  6. Sheet8.[A65536].End(xlUp).Offset(1, 0).Resize(, 13) = ar
  7. Next
  8. End With
  9. End Sub
½Æ»s¥N½X
¥t¥~³ø»ù³æbÄæ¦]¦³ªÅ¥Õ¦r²Å¡A©Ò¥H.[B26].End(xlUp)·|§PÂ_¿ù»~¡A¿ï¤¤b15:b26,«ö¤@¤Udelete.

TOP

        ÀR«ä¦Û¦b : ¤ß¤¤±`¦sµ½¸Ñ¡B¥]®e¡B·P«ä¡Bª¾¨¬¡B±¤ºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD