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

[µo°Ý] ¸ß°Ý¤@­Ó¬ö¿ý¶}°ª§C¦¬»ùªºVBA°ÝÃD

[µo°Ý] ¸ß°Ý¤@­Ó¬ö¿ý¶}°ª§C¦¬»ùªºVBA°ÝÃD

±Nµ{¦¡¼g¦b¤u§@ªí¨Æ¥óµ{§ÇCalculate¤¤

a2¸ê®Æ¨Ó·½·|¥Îdde³sµ²¬Ý½L³nÅ骺¦¨¥æ»ù

´ú¸Õªº®É­Ô§Ú¼È®É§â¸ê¾ú¨Ó·½³]©w¬°¥t¤@­ÓexcelÀɮתºa1Àx¦s®æ

¥u­n§Ú§ïÅܸê®Æ~~~¶}°ª§C¦¬ªº¬ö¿ýºâ¥¿±`

¦ý´N¬OµLªk°µ¨ì¨C¤@¬q©T©w®É¶¡(¨Ò¦p¨C1¤ÀÄÁ©Î¨C5¤ÀÄÁ¬Æ¦Ü¬O¨C5¬í)´N´«¤@¦Cªº³o­Ó°Ê§@

Áٽаª¤âÀ°¦£¬Ý¤@¤U¡ã~§Ú¿ù¦b­þ!!·PÁÂ!!

20150704.rar (16.66 KB)

¦^´_ 1# laieven
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Private Sub Worksheet_Calculate()
  3.     Dim StartTime As Date, StopTime As Date
  4.     Dim T(1 To 3) As Double, xMax As Double, xMin As Double
  5.     Dim ¦¨¥æ»ù As Range
  6.     Static Rng As Range, S As Integer
  7.    
  8.     StartTime = Range("A6") '¶}½L®É¶¡, ¨Ò¦p: "09:00:00 AM"
  9.     StopTime = Range("A7")  '¦¬½L®É¶¡, ¨Ò¦p: "01:30:00 PM"
  10.     If Time <= StartTime Or Time > StopTime Then '©|¥¼¶}½L ©Î ¤w¸g¦¬½L
  11.         Set Rng = Nothing
  12.         Exit Sub
  13.     End If
  14.     Set ¦¨¥æ»ù = Range("A2")
  15.      '****¤ÀÄÁ³]©w******
  16.     'T(1) = Application.Text(StartTime, "[M]")  '¶}½L®Éªº¤ÀÄÁ¼Æ
  17.     'T(2) = Application.Text(Time, "[M]")       'Nowªº¤ÀÄÁ¼Æ
  18.     'T(3) = Round(((T(2) - T(1)) / 5))          '¶}½L«á¨C®É5¤ÀÄÁªº¶¡¹j¦¸¼Æ
  19.       
  20.       '*** ¬í¼Æ³]©w
  21.     T(1) = Application.Text(StartTime, "[S]")    '¶}½L®Éªº¬í¼Æ
  22.     T(2) = Application.Text(Time, "[S]")         'Nowªº¬í¼Æ
  23.     T(3) = Round(((T(2) - T(1)) / 10))           '¶}½L«á¨C10¬íªº¶¡¹j¦¸¼Æ
  24.    
  25.     If Rng Is Nothing Then Set Rng = Range("D1").Resize(, 4)
  26.     xMax = Application.Max(Rng)                 '¾ú¥v³Ì°ª»ù
  27.     xMin = Application.Min(Rng)                 '¾ú¥v³Ì§C»ù
  28.    
  29.     If S = T(3) Then                               '³]©wªº®É¶¡¶¡¹j¤º
  30.         If ¦¨¥æ»ù > xMax Then Rng.Cells(1, 2) = ¦¨¥æ»ù      '³Ì°ª»ù
  31.         If ¦¨¥æ»ù < xMin Then Rng.Cells(1, 3) = ¦¨¥æ»ù      '³Ì°ª»ù
  32.         Rng.Cells(1, 4) = ¦¨¥æ»ù                            '¦¬½L»ù
  33.     ElseIf S <> T(3) Then
  34.         Set Rng = Rng.Offset(1)                   '¤U¤@¦C
  35.         Rng.Resize(, 4) = ¦¨¥æ»ù
  36.         Cells(Rng.Row, "c") = Time  '¬d¬Ý®É¶¡ªº¶¡¹j,¦p¤£»Ý­n¥i§R°£±¼
  37.     End If
  38.     S = T(3)
  39. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

ª©¥D¼ö±¡ªº¨ó§U,Åý§Ú¤j¦Y¤@Ååªü!
¦A¦¹¥ý¸U¤À·PÁ¤F!!!
§Ú±oªáÂI®É¶¡®ø¤Æ¤@¤Uª©¥Dªº¨ó§U!!
·P®¦·P®¦!!!!!
¾Ç²ß¥h!!!

TOP

        ÀR«ä¦Û¦b : ¨ü¤HÂI¤ô¤§®¦¡A¶··í´é¬u¥H³ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD