¦p¦ó§Q¥ÎVBAIJµoÀx¦s®æ²£¥Í¤é´Á.®É¶¡

man65boy

½Ð±Ð¦Ñ®v­Ì¦p¦ó¦b¤u§@ªíIJµoÀx¦s®æ«á¡A¹ïÀ³ªºÄæ¦ì²£¥Í¤é´Á.®É¶¡
1.BÄæ¿é¤J¤å¦r«á¡A¢ìÄæ¹ïÀ³Äæ¦ì´NÅã²{¤é´Á.®É¶¡¡ABÄæ¨S¿é¤J¡A
   DÄæ¹ïÀ³Äæ¦ì´N¤Ï¥Õ¡C
2.CÄæ¿é¤J¤å¦r«á¡AEÄæ¹ïÀ³Äæ¦ì´NÅã²{¤é´Á.®É¶¡¡ACÄæ¨S¿é¤J¡A
   EÄæ¹ïÀ³Äæ¦ì´N¤Ï¥Õ¡C
3.FÄ欰¦X­p­pºâ®É¶¡Äæ¦ì¡C

ÁÂÁ¦Ѯv­ÌÀ°¦£¡I
ªþÀÉ: 20160108.rar (16.42 KB)

¤U¸ü¦¸¼Æ: 19

2016-1-8 10:41

ML089

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. Dim r%, xD As Range, xE As Range, xF As Range
   
3. With Target
   
4.       If .Count > 1 Then Exit Sub
   
5.       If Intersect(Union([B:B], [C:C]), .Cells) Is Nothing Then Exit Sub
   
6.       Application.EnableEvents = False
   
7.       .Offset(0, 2).Value = Now()
   
8.       r = .Row() - 1: Set xD = [D1].Offset(r, 0): Set xE = [E1].Offset(r, 0): Set xF = [F1].Offset(r, 0)
   
9.       If Application.Count(xD, xE) = 2 Then xF = Int((xE - xD) * 24 * 60)
   
10.       Application.EnableEvents = True
    
11. End With
    
12. End Sub

½Æ»s¥N½X

man65boy

¦^´_ 2# ML089

¯u¦hÁÂML089¦Ñ®vªº¦^µª¡A¦pªG¿é¤J¤é´Á.®É¶¡«á¡A§Ú¦b¤â°Ê§ó§ï°_©l®É¶¡©Îµ²§ô®É¶¡¡A¥i§_Åý²Î­pªº"¤À"¡A¤]¦P®É¦P¨BÅܧó¥[Á`¡A³Â·Ð¦Ñ®v¤F^^

yen956

¦^´_ 2# ML089
·PÁ ML089¶Wª©ªºVBAªº

yen956

¸Õ¸Õ¬Ý!!

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.     Dim Lst As Integer, r As Integer
   
3.     Lst = [A65536].End(xlUp).Row
   
4.     If Target.Count > 2 Then Exit Sub  '¦pªG¤@¦¸§ïÅܦh®æ«h¨S§@¥Î
   
5.     r = Target.Row
   
6.     If r > Lst Then Exit Sub  '¦pªG³Ì¤U­±ªº³¡ªù¬°ªÅ¥Õ«h¨S§@¥Î
   
7.    
   
8.     '¦pªG§ïÅܪºÀx¦s®æ¤£¦b BÄæ ©Î CÄæ «h¨S§@¥Î
   
9.     If Target.Column <> 2 And Target.Column <> 3 Then Exit Sub
   
10.    
    
11.     '¦pªG Target ¬°ªÅ¥Õ, «h¹j¨âÄæ¤Ï¥Õ, ¨Ã²M°£[Fxx]
    
12.     If Target = "" Then
    
13.         Target.Offset(0, 2) = ""
    
14.         Target.Offset(0, 2).Interior.ColorIndex = 35
    
15.         Cells(r, 6) = ""
    
16.     Else
    
17.         Target.Offset(0, 2) = Now    '§_«h¹j¨âÄæ¨ú®ø¤Ï¥Õ, ¶ñ¤J²{¦b®É¶¡
    
18.         Target.Offset(0, 2).Interior.ColorIndex = xlNone
    
19.     End If
    
20.         
    
21.     '¦pªG [Dxx] ¤Î [Exx] ¤£¬OªÅ¥Õ, ¦b [Fxx] ­pºâ®É®t(¥H¤ÀÄÁ¬°³æ¦ì)
    
22.     If Cells(r, 4) <> "" And Cells(r, 5) <> "" Then
    
23.         Cells(r, 6) = Int((Cells(r, 5) - Cells(r, 4)) * 24 * 60)
    
24.     End If
    
25. End Sub

½Æ»s¥N½X

¤U¸ü (26.54 KB)

2016-1-8 15:55

ML089

¦^´_ 3# man65boy

FÄ檽±µ¥Î¤½¦¡±a¤J

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
      If .Count > 1 Then Exit Sub
      If Intersect(Union([B:B], [C:C]), .Cells) Is Nothing Then Exit Sub
      Application.EnableEvents = False
      .Offset(0, 2).Value = Now()
      Range("F" & .Row) = "=IF(COUNT(RC4:RC5)=2,INT((RC5-RC4)*24*60),"""")"
      Application.EnableEvents = True
End With
End Sub

¸É¥R¡G
³o¬O ¬Û¹ï¦ì¸m¤½¦¡
Range("F" & .Row) = "=IF(COUNT(RC[-2]:RC[-1])=2,INT((RC[-1]-RC[-2])*24*60),"""")"

³o¬O µ´¹ï¦ì¸m¤½¦¡
Range("F" & .Row) = "=IF(COUNT(RC4:RC5)=2,INT((RC5-RC4)*24*60),"""")"

man65boy

¦^´_ 5# yen956

ÁÂÁÂyen956¤j¤jªº¦^µª¡A¦ý¦pªG¤â°Ê§ó§ï®É¶¡ªº¸Ü¡A¦@­p"¤À"¥i§_¦P®É§ó§ï¼Æ¾Ú¡AÁÂÁ§A!

man65boy

¦^´_ 6# ML089


    ÁÂÁÂML089¤j¤jªº¦^µª¡Aµ¹¤p§Ì¦h¤F¤@¶µ¸ÑÃDªºµª®×!¯u·PÁÂ^^

ML089

¦^´_ 8# man65boy

³o¬O ¬Û¹ï¦ì¸m¤½¦¡
Range("F" & .Row) = "=IF(COUNT(RC[-2]:RC[-1])=2,INT((RC[-1]-RC[-2])*24*60),"""")"

³o¬O µ´¹ï¦ì¸m¤½¦¡
Range("F" & .Row) = "=IF(COUNT(RC4:RC5)=2,INT((RC5-RC4)*24*60),"""")"

yen956

¦^´_ 6# ML089
ÁÂÁ¤j¤jªº«ü¾É!!
¤×¨ä¬O¥Î
Range("F" & .Row) = "=IF(COUNT(RC4:RC5)=2,INT((RC5-RC4)*24*60),"""")"
ªº¨Ï¥Î, ¥i¥H²¤Æ¤£¤Ö If A and B then ... ªº¼gªk, «D±`¹ê¥Î, ¦¬¤U, ÁÂÁÂ