Hsieh

¦^´_ 1# naraohya


¤£ª¾¹D¦³¨S¦³²z¸Ñ¿ù»~
ª½±µÅýDÄæ°O¦í²Ä¤@¦¸BÄæ¿ïPD®Éªº¤é´Á(³o¥ÎCHANGE¨Æ¥ó¨Ó¿é¤JC¡ADÄ檺­È)
Sheet3¼Ò²Õ

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. If Target.Count > 1 Then Exit Sub
   
3. If Intersect(Target, [B3:B100]) Is Nothing Or Target = "" Then Exit Sub
   
4. Application.EnableEvents = False
   
5. If Target.Offset(, 1) = "" Then Target.Offset(, 1).Resize(, 2) = Array(IIf(Target = "PD", 1, ""), IIf(Target = "PD", Date, ""))
   
6. Application.EnableEvents = True
   
7. End Sub

½Æ»s¥N½X

¨C¦¸¶}ÀɮɭpºâDÄæ¤é´Á¨ì·í¤éªº¤Ñ¼Æ¨Ã§ó§ïDÄ欰·í¤é¤é´Á

1. Private Sub Workbook_Open()
   
2. Dim A As Range
   
3. With Sheet3
   
4. Application.EnableEvents = False
   
5. For Each A In .[B3:B100]
   
6.    If A = "PD" And IsDate(A.Offset(, 2)) Then _
   
7.    A.Offset(, 1) = _
   
8.    DateDiff("d", A.Offset(, 2), Date) + A.Offset(, 1): A.Value = "CL": A.Offset(, 2) = Date
   
9. Next
   
10. Application.EnableEvents = True
    
11. End With
    
12. End Sub

½Æ»s¥N½X

­pºâpending day.zip (11.84 KB)

¤U¸ü¦¸¼Æ: 5

2010-11-27 18:32