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

[µo°Ý] ½Ö¯àÀ°³o´X¥G­«½Æ°Ê§@ªºVBA½G¨­ ÁÂÁÂ

¦^´_ 10# don4831

«Ü·PÁ±zªº¥Î¤ß ¦ýµo¥Í¤F¿ù»~°T®§ ¦pªþ¹Ï
    lineBegin = Worksheets("Data").Range("K4") '°_¦æ
    lineEnd = Worksheets("Data").Range("L4") '¨´¦æ
   ³o2¦æK4 L4¬O§Ú§ïªº ¨º¬O°t¦X§Ú¥Ø«e¨Ï¥Îªº¿é¤J¦ì¸m


   

TOP

§Ú·Q§A²{¦b»Ý­nªº¡A¨Ã¤£¥u¬O³æ¯Âªºµ{¦¡¶}µo¡A¦Ó¬O¦p¦ó¨Ï¥Î¦³­­ªº¨t²Î¸ê·½«Ø¸m¤j¶qªº¸ê®Æ¡C
³o¬Oº¡°ª¶¥ªº°ÝÃD¸Ñ¨M§Þ¥©¡AµLªk²Ó»¡¡A¥u¯à´£¨Ñ¹ê¨Ò¡C
±N¿ý»sªº¥¨¶°½G¨­¡A¥¦¨Ï¥Îªº¨t²Î¸ê·½ÁÙ¬O«Ü°ª¡A
¥Ø«e¦b§ÚªºÀô¹Ò¤U¡A²£¥Í1000µ§¥ª¥k¤§¸ê®Æ¡A¨Ï¥Î¸ê·½¤Î°õ¦æ®Ä¯à©|¥i¡A
­Y¬O­n²£¥Í3200µ§ªº¸ê®Æ¡A¯Ó¥Î¬ù20¤À30¬í¤§«á¡A·|¥X²{°T®§¡G¡i°O¾ÐÅ餣¨¬¡j¡C
¥Î§Úªºµ{¦¡¡A§Ú¬O¥H1000µ§¬°³B²z³æ¦ì¡A¯Ó¥Î¬ù07¤À²£¥Í64000µ§¸ê®Æ¡A¥¿±`µ²§ô¡C
´£¿ô¡Gxls¤@­Ó¤u§@ªí³Ì¦h¥u¯à¦³65536µ§¸ê®Æ¡C
½Ð¦b¡mData¡n¤u§@ªí¤§I4¤ÎJ4Àx¦s®æ¿é¤J°_©l¦C¤Î²×¤î¦C¡C

Sub copyForNext()
    Rem buffer = 999¡G¨C1000µ§¬°1­Ó³B²z³æ¦ì
    Application.ScreenUpdating = False
    Call clearRow '²M°£Â¸ê®Æ
    buffer = 999
    lineBegin = Worksheets("Data").Range("I4") '°_¦æ
    lineEnd = Worksheets("Data").Range("J4") '¨´¦æ
    For i = 1 To 10
        Worksheets("ªR" & i).Activate
        Rows(2).Copy
        For j = lineBegin To lineEnd
            k = j + buffer
            Rows(j & ":" & k).Select
            Selection.PasteSpecial Paste:=xlPasteAll
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues
            j = k
        Next
        Rem ¨ú®ø½Æ»s¼Ò¦¡
        Application.CutCopyMode = False
    Next
    Application.ScreenUpdating = True
End Sub

Sub clearRow()
    Worksheets(Array("ªR1", "ªR2", "ªR3", "ªR4", "ªR5", "ªR6", "ªR7", "ªR8", "ªR9", "ªR10")).Select
    Rows("3:" & Rows.Count).Select
    Selection.Clear
    '¥H¤U°Ê§@¡A¥u¬O²M°£¥ý«eªº¿ï¾Ü¡A¥H§K¤H¤u­n¥h¨ú®ø¿ï¾Ü¡C
    Range("A1").Select '¥Øªº¡G¥ý«e¿ï¾Ü°Ï°ìªº¤Ï¼v¡A¥u¯d¿ï¾Ü"A1"¤§Àx¦s®æ
    Worksheets("ªR1").Select '¥Øªº¡G¥ý«e¿ï¾Ü¤Q­Ó¤u§@ªí¡A¥u¯d¤@­Ó¿ï¾Ü"ªR1"¤§¤u§@ªí
End Sub

A.png
[color=blue]KY[/color]

TOP

¦^´_ 12# don4831
¥i°õ¦æ§¹²¦ ¦ý²Î­pµ²ªG¿ù»~ ¤£ª¾¹D¨º­Ó¦a¤è¦³»~ §ÚÁÙ¦b§ä±z´£¨Ñªºµ{¦¡½X ¬Ý°ÝÃD¥X¦b¨ºùØ
ÁÂÁ±z¶O®É¶O¤OªºÀ°§U§Ú

TOP

¦^´_ 11# lcctno
½Ð½T»{¿é¤Jªº­È©M¸ê®Æ«¬ºA¬O§_¬°¼Æ¦r
[color=blue]KY[/color]

TOP

¦^´_ 13# lcctno
§Ú¤]¥¿¦n­nÁA¸Ñ§Úªº¨t²Î¸ê·½¤§·¥­­¡A¥H«Kµ{¦¡¤§¶}µo¡A§Aªºµ{¦¡¥¿¦n¥i¥H¨ó§U§Ú¶i¦æ¡AÁÂÁ¡C
[color=blue]KY[/color]

TOP

¥»©«³Ì«á¥Ñ lcctno ©ó 2015-8-27 17:44 ½s¿è

¦^´_ 12# don4831

§ä¨ì­ì¦]¤F
°õ¦æ²Ä3~1002¦C®É¥¿±`
·í°õ¦æ²Ä1003~2002¦C®É ´N¤£¥¿±`¤F ¬Ý¨ìªº¬O¶K¤Wªº­È»P °õ¦æ²Ä3~1002¦C¤§­È¬Û¦P
·í°õ¦æ²Ä2003~3002¦C®É ¦P¼Ëµ²ªG ¬Ý¨ìªº¬O¶K¤Wªº­È»P °õ¦æ²Ä3~1002¦C¤§­È¬Û¦P
À³¸Ó¥H¤U¤]·|¬O¦P¼Ëªºµ²ªG
§Úª¾¹DÀ³¸Ó¬O¶Kªº­È¿ï¿ù¥Ø¼Ð §Ú·QÁÙ¬O³Â·Ð±z°£¿ù §Ú¹ê¦b¬O¤£·|­×§ï³o»ò²`ªº°ÝÃD ÁÂÁ±z¤F

TOP

¥»©«³Ì«á¥Ñ lcctno ©ó 2015-8-27 21:34 ½s¿è

¦^´_ 12# don4831

Sub copyForNext()
    Application.ScreenUpdating = True '¦]¬°­n¬Ý°Ê§@¬O§_¥¿½T
    'Call clearRow '²M°£Â¸ê®Æ ¬Ý¯à¤£¯à¥u²M°£·í¤U­n°õ¦æªº¦a¤è §_«h¤§«e¤§­È´N³Q²M°£¤F Åܦ¨¥²¶·­n±qÀY°õ¦æ ¨º´NµLªkµ²¬Ù³B²z®É¶¡(¦]¬°¨C¶g¼W¥[©ó¤u§@­¶"Data"¤§¦C¼Æ¥u¦³¤Ö¼Æ´X¦C)

    lineBegin = Worksheets("Data").Range("K4") '°_¦æ §Ú¥Ø«e¨Ï¥Î¤§¦ì¸m
    lineEnd = Worksheets("Data").Range("L4") '¨´¦æ §Ú¥Ø«e¨Ï¥Î¤§¦ì¸m
    buffer = Worksheets("Data").Range("M4")
    Rem buffer = ¤u§@­¶"Data"¤ºM4¤§­È ¬°1­Ó³B²z³æ¦ì
  ¤è«K°£¿ù ¤Î°t¦X¥¼¨Ó¨C¶g¤p¶q¼W¥[ªº"Data"  ¥i´î¤Ö°õ¦æ®É¶¡
   
    For i = 1 To 10
        Worksheets("ªR" & i).Activate
        Rows(2).Copy
        For j = lineBegin To lineEnd
            k = j + buffer - 1
            Rows(j & ":" & k).Select
            Selection.PasteSpecial Paste:=xlPasteAll
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues
            j = k
        Next
        Rem ¨ú®ø½Æ»s¼Ò¦¡
        Application.CutCopyMode = False      
    Next
    Application.ScreenUpdating = True

    'Âk¦ì¨Ãµ²§ôSub
    Sheets("Data").Select

    End Sub

TOP

¦^´_ 17# lcctno

¬JµM³£¬O¦V¤Ucopy¥i¤£¥u½Æ»s¡B¶K¤W
¦ó¤£¦Ò¼{¦Û°Ê¶ñº¡AutoFill
  1. Sub xx()
  2. Dim j4%
  3. j4 = Sheets("DATA").[j4]
  4. For i = 1 To 10
  5.     With Sheets("ªR" & i)
  6.         dl = .[a65536].End(xlUp).Row
  7.         If j4 <> dl Then .Rows(dl & ":" & dl).AutoFill _
  8.         Destination:=.Rows(dl & ":" & j4), Type:=xlFillDefault
  9.     End With
  10. Next
  11. End Sub
½Æ»s¥N½X

TOP

¦^´_ 18# lpk187
ÁÂÁ±zªº«Øij
¦pªG¨Ï¥Î¦Û°Ê¶ñº¡¨ì²×¤î¦C ¨º7000¦C¬Æ¦Ü10000¦C  ¥i¯à¨S¦³´X¥x¹q¸£¯à¶¶§Q°õ¦æ§¹¦¨ ¦]¬°¤Ó¦û¹q¸£¸ê·½ ¥B¨C¦¸³£±o®ö¶O«Ü¦h®É¶¡ ¥u¬°¤F¦b¤À­¶"Data"¤¤¼W¥[¼Æ¦Cªº¸ê®Æ ¦Ó¥²¶·¥þ³¡¦A¹Bºâ¤@¦¸ ¥B§¹¥þ¨S¦³¿ï¾Ü©Ê

TOP

¦^´_ 17# lcctno

¥ý³B²z²Ä¤@­Ó°ÝÃD¡G
'Call clearRow '²M°£Â¸ê®Æ ¬Ý¯à¤£¯à¥u²M°£·í¤U­n°õ¦æªº¦a¤è §_«h¤§«e¤§­È´N³Q²M°£¤F Åܦ¨¥²¶·­n±qÀY°õ¦æ ¨º´NµLªkµ²¬Ù³B²z®É¶¡(¦]¬°¨C¶g¼W¥[©ó¤u§@­¶"Data"¤§¦C¼Æ¥u¦³¤Ö¼Æ´X¦C)
½Ð°Ý¡G§A©Ò¿×ªº·í¤U¬O¤°»ò¡H§A¤W¦¸´£¨ì­n¦Û²Ä3¦æ°_²M°£¸ê®Æ¡A§Ú¥u¬Ocall¥¦¡A¥H§K·s¸ê®Æ¤£¤À¡C
[color=blue]KY[/color]

TOP

        ÀR«ä¦Û¦b : ¤@¥y·Å·xªº¸Ü¡A´N¹³©¹§O¤H¨­¤WÅx­»¤ô¡A¦Û¤v·|ªg¨ì¨â¤Tºw¡C
ªð¦^¦Cªí ¤W¤@¥DÃD