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

[µo°Ý] ½Ð°Ý¦p¦ó±Nsheet1ªº¿é¤J¸ê®Æ¶K¤Wsheet2¯S©wÀx¦s®æ

[µo°Ý] ½Ð°Ý¦p¦ó±Nsheet1ªº¿é¤J¸ê®Æ¶K¤Wsheet2¯S©wÀx¦s®æ

¦pÃD­n¦p¦ó±N¾Ç¥Í¦¨ÁZ>60ªº,¨Ò¦p¿é¤J80¤À,§PÂ_¤Î®æ«á¶K¨ìsheet2ªºB5Àx¦s®æ,²Ä¤Gµ§¤Î®æªº¶K¨ìB6Àx¦s®æ,²Ä¤Tµ§¤Î®æ¶K¦ÜB7Àx¦s®æ,³Ì¦h¶K¤Tµ§,·í¤U¤@¦¸¦A¿é¤J·sªº¦¨ÁZ®É,¥ý²MªÅsheet2ªº¤Tµ§¸ê®Æ,¨Ã±q²Ä¤@µ§B5¦A¶}©l¶K¤W,¦p¦¹¤ÏÂаõ¦æ!¦pªþ¥ó.½Ð«e½ú¨ó§U.thanks!!
apple

¦^´_ 1# hpmatri

¦¨ÁZ¬O¨ú«e3¦W¡AÁÙ¬O¨ÌSheet1ªº±Æ¦Cªº«e¤T­Ó§Y¥i??

TOP

¥»©«³Ì«á¥Ñ ashan0418 ©ó 2014-12-11 17:59 ½s¿è

¦^´_ 1# hpmatri

¨ú«e¤T¦W¥Î

test.zip (8.36 KB)

TOP

¦^´_ 2# ashan0418


    ±z¦n:¿é¤J¸ê®Æ¬O¤@µ§¤@µ§¿é¤J,±Ä¥ý¶i¥ý¥X,²Ä¤@µ§¤Î®æ¶ñ¤Jb5,²Ä¤Gµ§¤Î®æ¶ñ¤Jb6,²Ä¤Tµ§¶ñ¤Jb7,²Ä¥|µ§¤Î®æ®É¥ý±Nsheet2«e¤Tµ§²MªÅ,²Ä¥|µ§¦A¶ñ¤Jb5,²Ä¤­µ§ ¶ñ¤Jb6,²Ä¤»µ§¶ñ¤Jb7,²Ä¤Cµ§¤Î®æ«á¦A±Nsheet2«e¤Tµ§²MªÅ,¦A±N²Ä¤Cµ§¶ñ¤Jb5,¨Ì¦¹Ãþ±À!
    ÁÂÁ§Aªº¦^µª,§Ú¥Ø«eÅv­­¤£¨¬µLªk¤U¸üÀÉ®×,¥i§_½Ð§A¶K¤W¨Ó³o,§Ú¦bcopy¨ìexcel test,ÁÂÁÂ!!
apple

TOP

  1. Dim i As Integer, last_row As Integer
  2. Private Sub Worksheet_Change(ByVal Target As Range)
  3.     With Target
  4.         last_row = Selection.SpecialCells(xlCellTypeLastCell).Row
  5.         
  6.         If Selection.Column = 3 And Cells(last_row, 3).Value >= 60 Then
  7.                 i = i + 1

  8.                 Sheets("sheet2").Select
  9.                 If i Mod 3 = 1 Then
  10.                     Sheets("sheet2").Cells.Delete Shift:=xlUp
  11.                     Sheets("sheet2").Range("B5").Activate
  12.                 End If
  13.                 Sheets("sheet1").Select
  14.                 Range(Cells(last_row, 1), Cells(last_row, 3)).Copy
  15.                
  16.                 Sheets("sheet2").Select
  17.                 ActiveCell.PasteSpecial
  18.                 ActiveCell.Offset(1).Select
  19.                
  20.                 Sheets("sheet1").Select
  21.         End If
  22.     End With
  23. End Sub
½Æ»s¥N½X
¦^´_ 4# hpmatri

TOP

¦^´_ 5# ashan0418


    ¤j¤j±z¦n:§Ú¦³§â±zµ{¦¡½X¶K¨ìexcel´ú¸Õ,¤£¹Lsheet1 ¿é¤J¦¨ÁZ«á¤£·|copy ¦bsheet2,¥i§_½Ð¤j¤j¦AÀ°§Ú´ú¸Õ¬Ý¬Ý,ÁÂÁÂ!!
apple

TOP

¦^´_ 6# hpmatri


©êºp¡A§Ñ¤F»¡©ú¡A¬O­n¶K¦bSheet1ªº¤À­¶¤¤!!
¦p¹Ï¡A½Ð¦A¸Õ¸Õ!!

Snap1.jpg

TOP

¦^´_ 4# hpmatri

¤u§@ªí1¼Ò²Õ
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. With ¤u§@ªí2
  3. If Target.Count = 1 And Not Intersect(Target, Columns("C:C")) Is Nothing Then
  4.     If Target >= 60 Then
  5.        k = Application.CountA(.Range("B5:B7"))
  6.        If k = 3 Then .Range("B5:D7").ClearContents: k = 0
  7.        Target.Offset(, -2).Resize(, 3).Copy .[B5].Offset(k)
  8.     End If
  9. End If
  10. End With
  11.       
  12. End Sub
½Æ»s¥N½X
play.gif
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 8# Hsieh

Hsiehª©¥D :

    ½Ð°Ý¡yIntersect(Target, Columns("C:C"))¡z³o¤@¬qªº¥\¯à¬O¤°»ò??

TOP

¦^´_ 9# ashan0418


¶Ç¦^¨â­ÓÀx¦s®æ½d³òªº¥æ¶°
¸Ô²Ó½Ð°Ñ¦ÒIntersect»¡©ú
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¦³®É·í«äµL®É­W¡A¦n¤Ñ­n¿n«B¨Ó³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD