[µo°Ý] ±Ï±Ï§Ú§a¡I¤é´Á½Æ»s¡]¸Ô±¡½Ð°Ñ¾\¤º¤å¡^

emmalee

¨BÆJ¤@¡G·í¨Ï¥ÎªÌ¦bb1¿é¤J¶}©l¤é´Á(¤£©w´Á)
¨BÆJ¤G¡G¨ìsheet1¤¤·j´M¨ìb1¤é´Á«á¿ï¨ú¶}©l¤é¨ì³Ì²×¤é´Átoday()=>¶À¦â©³¡A¨Ã½Æ»s
¨BÆJ¤T¡G§âsheet1¶À¦â©³½Æ»s¨ìsheet2ªºa3
½Ð°Ý¥¨¶°­n«ç»ò°µ???????:'(

Hsieh

¦^´_ 1# emmalee
SHEET1¤u§@ªí¼Ò²Õ

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. If Target.Address <> "$B$1" Then Exit Sub
   
3. With Sheet2
   
4. Set a = .Columns("A").Find(Target, LookIn:=xlValues)
   
5. Set b = .Columns("A").Find(Date, LookIn:=xlValues)
   
6. If Not a Is Nothing And Not b Is Nothing Then
   
7. Range([A3], Cells(Rows.Count, 2)) = ""
   
8. .Range(a, b.Offset(, 1)).Copy [A3]
   
9. End If
   
10. End With
    
11. End Sub

½Æ»s¥N½X

luhpro

¦^´_ 1# emmalee
¥H¤Uµ{¦¡©ñ¦b Sheet2 ¤º

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.   Dim iRow%
   
3.   
   
4.   With Target
   
5.     If .Column = 2 And .Row = 1 Then
   
6.       Application.EnableEvents = False
   
7.       With .Parent
   
8.         iRow = .Cells(Rows.Count, 1).End(xlUp).Row
   
9.         If iRow < 3 Then iRow = 3
   
10.         .Range(.[A3], .Cells(iRow, 2)).Clear
    
11.       End With
    
12.       Call GetDate(Target)
    
13.       Application.EnableEvents = True
    
14.       Target.Select
    
15.       
    
16.     End If
    
17.   End With
    
18. End Sub

½Æ»s¥N½X

¥H¤Uµ{¦¡©ñ¦b Module ¤º

1. Sub GetDate(ByVal rTar As Range)
   
2.   Dim iRow%
   
3.   Dim dDate As Date
   
4.   Dim vSheet1, rFind As Range
   
5.   
   
6.   On Error Resume Next
   
7.   Set vSheet1 = Sheets("Sheet1")
   
8.   
   
9.   With vSheet1
   
10.     iRow = .Cells(Rows.Count, 1).End(xlUp).Row
    
11.     Set rFind = Nothing
    
12.     dDate = rTar.Value
    
13.     Do Until rFind.Row > 1
    
14.       Set rFind = Range(.[A2], .Cells(iRow, 1)).Find(dDate, LookIn:=xlValues)
    
15.       dDate = dDate + 1
    
16.     Loop
    
17.       .Range(rFind, .Cells(iRow, 2)).Copy
    
18.       rTar.Parent.[A3].PasteSpecial Paste:=xlPasteAll
    
19.   End With
    
20. End Sub

½Æ»s¥N½X

emmalee

¤£¦n·N«ä¡A¤£¤ÓÀ´¡A¬O¤£¬O¶K¨ì¥¨¶°´N¥i¥H¥Î¤F?
¤£¦n·N«ä¡A¬O¨â­Ó³£¶K¨ì¥¨¶°¸Ì­±¶Ü?¦ý¬O§ÚµLªk¨Ï¥Î??
¥t¥~¥i¥H¸Ô¸Ñµ¹§ÚÅ¥¶Ü?ÁÂÁÂ?

Hsieh

¦^´_ 4# emmalee


   

¤U¸ü (631.87 KB)

2011-6-9 23:59