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

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

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

½Ð±Ð¦Ñ®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)

  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
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 2# ML089

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

TOP

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

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2016-1-8 15:55 ½s¿è

¸Õ¸Õ¬Ý!!
  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
test.gif
2016-1-8 15:55

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2016-1-9 07:38 ½s¿è

¦^´_ 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),"""")"
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 5# yen956

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

TOP

¦^´_ 6# ML089


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

TOP

¦^´_ 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),"""")"
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

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

TOP

        ÀR«ä¦Û¦b : ¡i®É¤é²öªÅ¹L¡j¤@­Ó¤H¦b¥@¶¡°µ¤F¦h¤Ö¨Æ¡A´Nµ¥©ó¹Ø©R¦³¦hªø¡C¦]¦¹¥²¶·»P®É¶¡Ävª§¡A¤Á²ö¨Ï®É¤éªÅ¹L¡C
ªð¦^¦Cªí ¤W¤@¥DÃD