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

hpmatri

¦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!!

¤U¸ü (56.82 KB)

2014-12-11 16:29

ashan0418

¦^´_ 1# hpmatri

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

ashan0418

¦^´_ 1# hpmatri

¨ú«e¤T¦W¥Î

hpmatri

¦^´_ 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,ÁÂÁÂ!!

ashan0418

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. 
   
9.                 Sheets("sheet2").Select
   
10.                 If i Mod 3 = 1 Then
    
11.                     Sheets("sheet2").Cells.Delete Shift:=xlUp
    
12.                     Sheets("sheet2").Range("B5").Activate
    
13.                 End If
    
14.                 Sheets("sheet1").Select
    
15.                 Range(Cells(last_row, 1), Cells(last_row, 3)).Copy
    
16.                
    
17.                 Sheets("sheet2").Select
    
18.                 ActiveCell.PasteSpecial
    
19.                 ActiveCell.Offset(1).Select
    
20.                
    
21.                 Sheets("sheet1").Select
    
22.         End If
    
23.     End With
    
24. End Sub

½Æ»s¥N½X

¦^´_ 4# hpmatri

hpmatri

¦^´_ 5# ashan0418


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

ashan0418

¦^´_ 6# hpmatri


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

¤U¸ü (18.98 KB)

2014-12-16 09:20

Hsieh

¦^´_ 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

¤U¸ü (325.56 KB)

2014-12-16 09:37

ashan0418

¦^´_ 8# Hsieh

Hsiehª©¥D :

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

Hsieh

¦^´_ 9# ashan0418


¶Ç¦^¨â­ÓÀx¦s®æ½d³òªº¥æ¶°
¸Ô²Ó½Ð°Ñ¦ÒIntersect»¡©ú