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

[µo°Ý] ¬O§_¦³¿ìªk®Ø¿ï½d³ò¥H­Èªº¤è¦¡¤U²¾¤£§ïÅÜÀx¦s®æ¤½¦¡??

[µo°Ý] ¬O§_¦³¿ìªk®Ø¿ï½d³ò¥H­Èªº¤è¦¡¤U²¾¤£§ïÅÜÀx¦s®æ¤½¦¡??

[attach]16843[/attach]

¦]¬°³øªí¤º§t¦³«Ü¦h¤½¦¡¤Î«Ü¦hvoolkupªºÄæ¦ì ¤]¦³®É¶¡­pºâªº®æ¦¡§Ú¸ÕµÛ´¡¤J¦C«á¤U²¾¤½¦¡  ¤½¦¡·|¾ã­Ó¶Ã±¼
¬O§_¦³¿ìªk®Ø¿ï§Ú­n¤U²¾ªº½d³ò¦C¼Æ «ö«ö¶s«á ¦Û°Ê±N²Ä7Äæ ¸ò²Ä15Äæ(ºñ¦âÄæ¦ì)¤Î§Ú©Ò®Ø¿ïªº¦C¼Æ¥H­Èªº¤è¦¡¤U²¾©ÎªÌ¤W²¾
¦Ó¤W©Î¤U¦p¦³¸ê®Æ¦Ó¤£Âл\

¥»©«³Ì«á¥Ñ a31075ha ©ó 2013-11-23 16:25 ½s¿è


½d¨Ò¹Ï

TOP

¦^´_ 2# a31075ha

À³¸Ó¬O»¡®Ø¿ï«á ¥u­pºâ­n¤U²¾ªº¦C¼Æ¦Ó·|¤U²¾ªº¥u¦³²Ä7¸ò²Ä15Äæ ¦³ÂI»yµL­Û¦¸ = ="

TOP

¦]¬°³øªí¤º§t¦³«Ü¦h¤½¦¡¤Î«Ü¦hvoolkupªºÄæ¦ì ¤]¦³®É¶¡­pºâªº®æ¦¡§Ú¸ÕµÛ´¡¤J¦C«á¤U²¾¤½¦¡  ¤½¦¡·|¾ã­Ó¶Ã ...
a31075ha µoªí©ó 2013-11-23 16:17

a31075ha »¡ :
¦Ó¤W©Î¤U¦p¦³¸ê®Æ¦Ó¤£Âл\ <=== ³o¥y¸Ü¤£©ö²z¸Ñ, §Æ±æ¯àÁ|¨Ò¤@¤U.
¦Ü©ó©¹¤U²¾°Êªº¤è¦¡,³o¸Ì©ß¿j¤Þ¥É¤@¤U: (¥H¤Uµ{¦¡ Mark °Ï°ì¤£­­©Ò¿ïªºÄæ¦ì,¥u¨ú©Ò§@¥Îªº¦C½d³ò)
  1. Sub ©¹¤U²¾°Ê()
  2.   Dim lRow&, lRows&
  3.   Dim rTar As Range

  4.   Set rTar = Selection
  5.   If rTar.Count = 1 Then
  6.     lRows = 1
  7.   Else
  8.     lRows = UBound(rTar.Value2, 1)
  9.   End If
  10.   icol = 7
  11.   Do While icol < 16
  12.     Range(Cells(rTar.Row, icol), Cells(Rows.Count, icol).End(xlUp)).Copy _
  13.     Cells(rTar.Row + lRows, icol)
  14.     'Range(Cells(rTar.Row, icol), Cells(rTar.Row + lRows - 1, icol)) = ""  ' ¤£½T©w§A¬O§_­n²M±¼³o¨Ç¸ê®Æ
  15.     icol = icol + 8
  16.   Loop
  17. End Sub
½Æ»s¥N½X

TOP

¦^´_ 4# luhpro


¤£ª¾¹D¬O§_¦³¿ìªk¹³¹Ï¤¤¶Â¦â³¡¥÷¤@¼Ë ¤U²¾«á¤£¬OÅܦ¨2­Ó5¦Ó¬O±N­ì¥»ªº²M°£±¼

¦Ó¤W©Î¤U¦p¦³¸ê®Æ¦Ó¤£Âл\<<---¥H¤U½d¨Ò

¶À¦â³¡¥÷¬O¤w§¹¦¨»s§@ªº³¡¤À ºñ¦â³¡¤À¬O¥¿¦b»s§@
¦pªG©¹¤W²¾°Ê¬O§_¦³¿ìªk²¾°Ê¨ìºñ¦â³¡¤À¦³¸ê®Æ«á´N¤£¦A²¾°Ê ¤~¤£·|³y¦¨§â§¹¦¨ªº¸ê®ÆÂл\±¼©ÎªÌÅܰʨì.

·PÁ¤j¤j§A ¦]§ï¦¨¦Û±a¸ê®Æ¸ò¤½¦¡ªºªí®æ«á ¤~µo²{±`»Ý­n§ó§ï¶¶§Ç ¦Ó´¡¤J·|¾ã­Ó¶Ã±¼...= ="

TOP

¦^´_  luhpro
¤£ª¾¹D¬O§_¦³¿ìªk¹³¹Ï¤¤¶Â¦â³¡¥÷¤@¼Ë ¤U²¾«á¤£¬OÅܦ¨2­Ó5¦Ó¬O±N­ì¥»ªº²M°£±¼
¦Ó¤W©Î ...
a31075ha µoªí©ó 2013-11-24 22:21

1. ­n±N­ì¥»ªº¸ê®Æ²M°£±¼, §â¤W­± 4# ©Ò´£µ{¦¡ 15. ¤¤ªº ' ¥h±¼´N¬O§A­nªº¥\¯à¤F:

Range(Cells(rTar.Row, icol), Cells(rTar.Row + lRows - 1, icol)) = ""  ' ¤£½T©w§A¬O§_­n²M±¼³o¨Ç¸ê®Æ

2. ¦Ó¤W©Î¤U¦p¦³¸ê®Æ¦Ó¤£Âл\<<---¥H¤U½d¨Ò
¨ä¤¤ "¤W¦p¦³¸ê®Æ¦Ó¤£Âл\",
¨Ì¤U¹Ï©Ò­z¬Ý°_¨Ó§Ú²q§A­nªºÀ³¸Ó¬O :
¥h±¼ªÅ¥ÕªºÀx¦s®æ.
­Y¬Oªº¸Ü, ¨º¦¹®ÉÀ³¸Ó¬O¤£»Ý­nºÞ¨Ï¥ÎªÌ¨ì©³¬O Mark ­þ¨ÇÀx¦s®æ,
³£¥u°µ ¥h±¼ªÅ¥ÕªºÀx¦s®æ ³o­Ó°Ê§@,
¥H¤Uµ{¦¡°w¹ï G »P O ¨âÄæ¥h±¼©Ò¦³ªºªÅ¥ÕÀx¦s®æ.
  1. Sub ©¹¤W²¾°Ê()
  2.   Dim lRow&, lBRow&, lERow&
  3.   Dim rTar As Range
  4.   
  5.   icol = 7
  6.   Do While icol < 16
  7.     lRow = Cells(5, icol).End(xlDown).Row
  8.     lERow = Cells(Rows.Count, icol).End(xlUp).Row
  9.     lBRow = Cells(lRow, icol).End(xlDown).Row
  10.    
  11.     Do While lRow <> lERow
  12.       Range(Cells(lBRow, icol), Cells(lERow, icol)).Copy Cells(lRow + 1, icol)
  13.       Range(Cells(lERow - lBRow + lRow + 2, icol), Cells(lERow, icol)) = ""
  14.       lRow = Cells(5, icol).End(xlDown).Row
  15.       lERow = Cells(Rows.Count, icol).End(xlUp).Row
  16.       lBRow = Cells(lRow, icol).End(xlDown).Row
  17.     Loop
  18.     icol = icol + 8
  19.   Loop
  20. End Sub
½Æ»s¥N½X
¤£¹L, "¤U¦p¦³¸ê®Æ¦Ó¤£Âл\" - ³o­Ó¥i¯à·|¦³ºÃ¼{,
¥H¶Â®æ¨ºÄæ¨Ó¬Ý,­ì¥ýªº±Æ¦C¬O:

1 2 3 4 ªÅ®æ 5 6 7 8 ~

­Y§Ú Mark 3 4 ¨º¨â­ÓÀx¦s®æªº¦C,
¦A«ö ©¹¤U²¾°Ê ¶s,
¨ºµ²ªG¨ì©³À³¸Ó¬O :

1 2 ªÅ®æ ªÅ®æ 3 4 ªÅ®æ 5 6 7 8 ~  (3 ¤Î¨ä¥H¤U¥þ³¡©¹¤U²¾¨â¦C)

ÁÙ¬O

1 2 ªÅ®æ ªÅ®æ 3 5 6 7 8 ~  (3 »P 4 ©¹¤U²¾¨â¦C, ¦ý 4 ©Ò²¾¥Øªº®æ¤w¦³ 5 ©Ò¥H¤£Âл\, 3 Åܤ£¨£¤F)

©O?


¥t¥~, §Ú­Ëı±o§A­nªº¥i¯à¥u¬O:
´¡¤J¤@¨Ç¸ê®Æ¨ì¥Ø«eªº¸ê®Æ§Ç¦C¤¤,
­Y¯u¬O³o­Ó»Ý¨D, ¨ºµ{¦¡¤S·|»P¤W­±¤£¤@¼Ë¤F.

¤£ª¾§Aªº»Ý¨D¬O­þ­Ó?

TOP

¦^´_ 6# luhpro


    ¤j¤j·PÁ§A ³o¼Ë´N«Ü°÷¥Î¤F   ¦]¬°­ì¥»·Q»¡·|¬O¿ï¨ú½d³ò¾ã­Ó©¹¤U²¾°Ê ©Ò¥H©È·|¦³¸ê®Æ¨S³Q¿ï¨ì¦Ó³QÂл\±¼
    ¤£¹L¬JµM¾ã­Ó©¹¤U ¨º´N¤£·|¦³³o­Ó°ÝÃD¤F....©¹¤W¥u­n¤£¥h°Ê¨ì¤w¸g§¹¦¨ªº¸ê®Æ ©Ò¥H§R±¼ªÅ®æ¤]¸Ñ¨M¤F³o­Ó°ÝÃD
Á`¤§·PÁ¤j¤j¸Ñ¨M§Úªº§xÄÇ ªí³æ¥Î¤F¤Ó¦h¤½¦¡ ¨ç¦¡¤F ... = ="

TOP

¦^´_ 6# luhpro

¥t¥~, §Ú­Ëı±o§A­nªº¥i¯à¥u¬O:
´¡¤J¤@¨Ç¸ê®Æ¨ì¥Ø«eªº¸ê®Æ§Ç¦C¤¤,
­Y¯u¬O³o­Ó»Ý¨D, ¨ºµ{¦¡¤S·|»P¤W­±¤£¤@¼Ë¤F.
----------------------------------------------
¤j¤j§Úªº½T¬O­n´¡¤J¤@¨Ç¸ê®Æ¨ì¥Ø«eªº¸ê®Æ§Ç¦C¤¤
¥u¬O¦]¬°ªí³æ¨½´N³o¤GÄ榡¥²¶·¦Û¤vKEY¤W¥h ¨ä¥L³£¬O¤½¦¡³]©w¦n´«ºâ
§Ú¸Õ¹L´¡¤J¦C«á¤U©Ô¤½¦¡¤]·|¶]±¼ «Ü¦h¤]·|´«ºâ¤W¤@¦C¸ê®Æ ¸ò®É¶¡´«ºâ..
©Ò¥H¤~·Q¦³¬Æ»ò¿ìªk¥i¥H¤W¤U²¾°Ê¸ê®Æ

TOP

¦^´_  luhpro
¤j¤j§Úªº½T¬O­n´¡¤J¤@¨Ç¸ê®Æ¨ì¥Ø«eªº¸ê®Æ§Ç¦C¤¤ ...
a31075ha µoªí©ó 2013-11-26 01:37

¨Ì§Aªº»Ý¨D­«¼gµ{¦¡¦p¤U:
¥H¤Uµ{¦¡©ñ¦b Module
  1. Public bOK As Boolean
  2. Public rTar As Range
½Æ»s¥N½X
¥H¤Uµ{¦¡©ñ¦b­n´¡¤J¸ê®ÆªºSheet
  1. Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
  2.   Set rTar = Target
  3.   ufMain.Show
  4.   If bOK Then Cancel = True
  5. End Sub
½Æ»s¥N½X
¥H¤Uµ{¦¡©ñ¦b Module (§ó¦W¬° ufMain, «Ø¨â­ÓTextBox ¤À§O©R¦W¬° tbColG »P tbColO, «Ø¨â­Ó Buttom ¤À§O©R¦W¬° cbCancel »P cbOK )
  1. Private Sub cbCancel_Click()
  2.   bOK = False
  3.   ufMain.Hide
  4. End Sub

  5. Private Sub cbOK_Click()
  6.   Dim lRow&, lRows&, lI&
  7.   Dim arData
  8.   
  9.   bOK = True
  10.   If tbColO.Text = "" Then tbColO.Text = tbColG.Text
  11.   With rTar.Parent
  12.     icol = 7
  13.     arData = Split(tbColG.Text, Chr(13) & Chr(10))
  14.     lRows = UBound(arData, 1)
  15.     lRow = rTar.Row
  16.     .Range(.Cells(lRow, icol), .Cells(Rows.Count, icol).End(xlUp)).Copy .Cells(lRow + lRows + 1, icol)
  17.     .Range(.Cells(lRow, icol), .Cells(lRow + lRows, icol)) = ""
  18.     .Cells(lRow, icol).Resize(lRows + 1) = Application.Transpose(arData)
  19.    
  20.     icol = 15
  21.     arData = Split(tbColO.Text, Chr(13) & Chr(10))
  22.     lRows = UBound(arData, 1)
  23.     lRow = rTar.Row
  24.     .Range(.Cells(lRow, icol), .Cells(Rows.Count, icol).End(xlUp)).Copy .Cells(lRow + lRows + 1, icol)
  25.     .Range(.Cells(lRow, icol), .Cells(lRow + lRows, icol)) = ""
  26.     .Cells(lRow, icol).Resize(lRows + 1) = Application.Transpose(arData)
  27.    
  28.     With ufMain
  29.       .tbColG.Text = ""
  30.       .tbColO.Text = ""
  31.       .Hide
  32.     End With
  33.   End With
  34. End Sub
½Æ»s¥N½X
IJµo¤è¦¡¬° :
1. ¥ô·N Mark Àx¦s®æ¨Ã¹ï Mark ªº°Ï°ì«ö·Æ¹«¥kÁä. (µ{¦¡¥u·|¥Î¨ì²Ä¤@­ÓÀx¦s®æªº¦C¸¹, §@¬°´¡¤J¸ê®Æªº°_©l¦C¸¹)
2. µ{¦¡·|¶}¤@­Ó UserForm ¤º§t¨â­Ó¿é¤J°Ï, ¤À§O¥Î©ó¿é¤J­n´¡¤J ÄæG »P ÄæO ªº¸ê®Æ.
(­Y­n´¡¤Jªº¸ê®Æ¤£¥u¤@µ§, ½Ð«ö Ctrl+Enter ´«¦C¿é¤J, ¨C¤@¦Cµø¬°¤@µ§¸ê®Æ, ¨âÄ檺¸ê®Æ "µ§¼Æ" ¥i¥H¤£¬Ûµ¥,
­Y ÄæOªº¿é¤J°Ï ¥¼¿é¤J¸ê®Æ, «h·|±N ÄæG ªº¸ê®Æ Copy ¹L¥h ÄæO , §Y¨âÄæ³£·|·s¼W¬Û¦Pªº¸ê®Æ.)
3. ­Y«ö¤U "¨ú®ø" «ö¯Ã,  UserForm ·|Ãö³¬, ±µµÛ«ì´_Åã¥Ü¤@¯ë "¦bÀx¦s®æ«ö¥kÁä" «á·|Åã¥Üªº§Ö±¶¦C (§Y¤´¦³«O¯d­ì¥ý«ö·Æ¹«¥kÁ䪺¥\¯à).
4. ­Y«ö¤U "½T©w" «ö¯Ã, ·|¥ý°õ¦æ´¡¤J¸ê®Æªº§@·~, ¦AÃö³¬ UserForm, ¥B§Ö±¶¦C§ï¬°¤£Åã¥Ü.

½d¨Òµ{¦¡¦p¤U:
MoveVol-a.zip (13.71 KB)

TOP

¦^´_ 9# luhpro


    ·PÁ§A §Ú¦A¸Õ¬Ý¬Ý.....µæ³¾ºN¯Á¤¤ ·P®¦..............

TOP

        ÀR«ä¦Û¦b : ¥Í®ð¡A´N¬O®³§O¤Hªº¹L¿ù¨ÓÃg»@¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD