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

¤@¬qÃö©ó¶}½L»ù³Ì°ª»ù³Ì§C»ù¦¬½L»ùªºvbµ{¦¡½X½Ð±Ð

¤@¬qÃö©ó¶}½L»ù³Ì°ª»ù³Ì§C»ù¦¬½L»ùªºvbµ{¦¡½X½Ð±Ð

Private Sub Worksheet_Calculate()
        Dim NowDateTime, nowTime, startTime, stopTime
        Dim tr As String

        NowDateTime = Now '²{¦bªº®É¶¡­È, ¥h±¼¾ã¼Æ³¡¥÷,
        nowTime = (NowDateTime - Int(NowDateTime)) '±o¨ì²{¦bªº®É¶¡­È
        startTime = Range("A6") '¶}½L®É¶¡, ¨Ò¦p: "09:00:00 AM"
        stopTime = Range("A8")  '¦¬½L®É¶¡, ¨Ò¦p: "01:30:00 PM"

        If nowTime <= startTime Then '©|¥¼¶}½L
                Exit Sub
        ElseIf nowTime > stopTime Then '¤w¸g¦¬½L
                Exit Sub
        ElseIf [A2] <> "-" And [A2] <> "###" Then '²M½Lªºª¬ºA, ¤£¨ú¨ä¸ê®Æ
                tr = Int((nowTime - startTime) * 288) + 2 '¨C®t 300 ¬í´N´«¤@¦C
                If Range("D" & tr) = "" Then
                        Range("D" & tr) = Range("A2")        '¶}©l»ù
                End If
                If Range("E" & tr) = "" Or Range("A2") > Range("E" & tr) Then
                        Range("E" & tr) = Range("A2")        '³Ì°ª»ù
                End If
                If Range("F" & tr) = "" Or Range("A2") < Range("F" & tr) Then
                        Range("F" & tr) = Range("A2")        '³Ì§C»ù
                End If
                Range("G" & tr) = Range("A2")                'µ²§ô»ù
        End If
End Sub


°ÝÃD:
1.Private Sub Worksheet_Calculate()
        Dim NowDateTime, nowTime, startTime, stopTime
        Dim tr As String

³o¤@°Ïªº·N¸q¬O?
²Ä¤@±Æªºdim¨S¦³¼g¥X¸ê®Æ«¬ºA¬O¤°»ò
²Ä¤G±Æ¬O©w¸qtrªº¸ê®Æ«¬ºA¬°¦r¦ê

³o¤@°Ï¬O«Å§i¦b³o¾ã±i¤u§@ªí³£¾A¥Îªº·N«ä¶Ü

2. ElseIf [A2] <> "-" And [A2] <> "###" Then '²M½Lªºª¬ºA, ¤£¨ú¨ä¸ê®Æ
³o¤@¬q¸Ìªº"###"¬O¤°»ò·N«ä©O?~~«e­±ªº·N«ä¬O¼ga2¤£µ¥©óªÅ¥Õ~~and a2¤£µ¥©ó###~~¨º½Ð°Ý###¬O¤°»ò·N«äªü??

3.tr = Int((nowTime - startTime) * 288) + 2 '¨C®t 300 ¬í´N´«¤@¦C
start=¦­¤W9ÂI~~nowtime=²{¦bªº®É¶¡~~*288+2~~³o¤@¦ê§Ú¤£¤j¯à²z¸Ñ~~Áٽаª¤â¯à¨ó§U¸ÑÄÀ¤@¤U¶Ü?·PÁ¤F

°ÝÃD«Ü°ò¦~~­Ó¤H¦Û¾Ç´X­Ó¤ë~~«Ü¦h¤@ª¾¥b¸Ñ~~°ÝÃD­Y¤Ó²Â~~ÁٽШ£½Ì

¥»©«³Ì«á¥Ñ luhpro ©ó 2015-5-5 23:26 ½s¿è
Private Sub Worksheet_Calculate()
        Dim NowDateTime, nowTime, startTime, stopTime
        Di ...
laieven µoªí©ó 2015-5-5 16:32
²Ä¤@±Æªºdim¨S¦³¼g¥X¸ê®Æ«¬ºA¬O¤°»ò

¨S¦³¼g¥X¸ê®Æ«¬ºAªº ´N¬O Variant, ¾A¥Î½d³ò³Ì¼s, ¦ý³Ì¯Ó°O¾ÐÅéªÅ¶¡.

³o¤@°Ï¬O«Å§i¦b³o¾ã±i¤u§@ªí³£¾A¥Îªº·N«ä¶Ü

¦b Sub °Ï¶ô¤ºªºÅܼƩw¸q¥u¯à¾A¥Î©ó¸Ó Sub °Ï¶ô,
¥þ°ìÅܼƤ@¯ë³£¬O©w¸q¦b Module °Ï¶ô¤º¥B¥H Public «O¯d¦r§@¶}ÀY.


³o¤@¬q¸Ìªº"###"¬O¤°»ò·N«ä©O?~~

³oÀ³¸Ó¬O¬Ý½L³nÅéÅã¥Ü¥X¨Ó±a¦³¯S©w·N¸qªºªí¥Ü¤è¦¡,
­n¬Ý§Aµ{¦¡¬O±q­þ¨Óªº?¦³¨S¦³»¡¬O­þ®M¬Ý½L³nÅé?

tr = Int((nowTime - startTime) * 288) + 2 '¨C®t 300 ¬í´N´«¤@¦C
start=¦­¤W9ÂI~~nowtime=²{¦bªº®É¶¡~~*288+2~~³o¤@¦ê§Ú¤£¤j¯à²z¸Ñ~~

­Y¥H¤@­Ó¼Æ¦r¨Óªí¥Ü®É¶¡­È,¾ã¼Æ³¡¤Àªí¥Ü¤é´Á,¤p¼Æ³¡¤À«hªí¥Ü®É¶¡
(24*60*60)/300=288
®É   ¤À  ¬í   
  ¤@      ¤Ñ

TOP

¦^´_ 2# luhpro


    ¥ý·PÁÂ"luhpro"ªº¼ö¤ß¨ó§U!!!·P®¦!!

1.288ªº¬O¨C5¤ÀÄÁªº·N«ä³o§Ú¯à²z¸Ñ~~§Ú¥u¬O¹ï³o¦ê"tr = Int((nowTime - startTime) * 288) +2"
¤£¤jÀ´(³o°ÝÃD¦ü¥G¦³ÂIÄø¡ã¤£¦n·N«ä)
int¬O­Ó¨ú¾ã¼Æªº¨ç¼Æ

¬°¦ó¥Î²{¦bªº®É¶¡¥h´î±¼¶}©lªº®É¶¡~~³o¤£´N±o¨ì¤F±q¶}©l¨ì²{¦bÁ`¦@¤w¸g¨«¤F¦h¤Ö®É¶¡¹ï¶Ü!

¨º¬°¦ó*288~~~°²³]9ÂI¶}©l~~²{¦b¬O9ÂI5¤À~~¾ã­Ó¦¡¤l¤£´NÅܦ¨int(5*288)+2~~~¤£¤j¯à²z¸Ñ®º????ÁÙ¦³¨º­Ó+2¬O­n·F¹À¥Î©O?


2.³]¦ntr¤§«á~~´N§â¥¦©ñ¨ì«á­±
" If Range("D" & tr) = "" Then
                        Range("D" & tr) = Range("A2")  

µ¥µ¥µ¥ªº¦¡¤l¸Ì~~~¦ü¥G¬O¥Î­pºâ«áªºµ²ªG¨Ó°µ¬°Àx¦s®æªº¦ì¸m­pºâ¥Î~~Åý¾Þ§@ªºÀx¦s®æ
¥i¯à¬Od5©Îd8©Îd2¤£µ¥ªº¦ì¸m~~°²³]­pºâµ²ªG¬O5³o¤@¬q¬O§Æ±æ³o¤@¬q®É¶¡¤º¾ã­Ó­pºâ¬yµ{¥u¦bd5-e5-f5-g5³o¤@¦C°µ¤j¤pµ§°O¨Ã±Æ§Ç~~¹ï¶Ü?

TOP

¦^´_ 3# laieven
¤£¬Oªº³á.
¤W­±»¡¹L :
­Y¥H¤@­Ó¼Æ¦r¨Óªí¥Ü®É¶¡­È,¾ã¼Æ³¡¤Àªí¥Ü¤é´Á,¤p¼Æ³¡¤À«hªí¥Ü®É¶¡
¦b­ì¦¡ tr = Int((nowTime - startTime) * 288) + 2 '¨C®t 300 ¬í´N´«¤@¦C  ¤¤,
tr ·|¨C 300 ¬í´N¥[ 1(¤]´N¬O ¨C®t 300 ¬í´N´«¤@¦C ªº·N«ä),
Äpµ²´N¦b©ó "¤p¼Æ³¡¤À«hªí¥Ü®É¶¡" ³o¤@ÂI,
·í®É¶¡®tªº¤p¼Æ³¡¤À­¼¤W 288 ±µµÛ¦A¨ú¾ã¼Æ¤§«á´N·|¥X²{³o¼Ëªºµ²ªG.
§A¥i¥H°õ¦æ¥H¤Uµ{¦¡¬Ý¬Ýµ²ªG:
  1. Sub nn()
  2.   Dim tr&
  3.   Dim startTime As Date
  4.   
  5.   startTime = Now
  6.   Do While tr <= 20
  7.     tr = Int((Now - startTime) * 28800) + 2 '¨C®t 3 ¬í´N´«¤@¦C
  8.     Cells(tr, 1) = tr
  9.   Loop
  10. End Sub
½Æ»s¥N½X
·í§Ú­Ì¹ï©óµ{¦¡¬Y¨Ç³¡¤À¦³ºÃ¼{ªº®É­Ô,
³Ì²³æªº¤è¦¡´N¬O¥Î F8 (³æ¨B¼Ò¦¡) Try Try ¬Ý,
§Ú³Ì±`¥Îªº¤è¦¡¬O¥ý¦b "§Y®É¹Bºâ" µøµ¡¤¤¥Î ? ¨Ó½T»{¤U¤@¦C¬ÛÃöÅܼƷ|Åܦ¨¬Æ»ò­È,
©Î¬O¤U¤@¦C·Q­n°µ¬Æ»ò¨Æ, (¨Ò¦p­Y¬O Cells ©Î Range «h½T»{­n§@¥ÎªºÀx¦s®æ¬O­þ¤@­Ó<¨Ç>)
¦A«ö¤@¦¸ F8 ½T»{µ²ªG¬O§_»P§Ú©Ò¹w´Áªº¬Û¦P.

TOP

¦^´_ 4# luhpro


    ·PÁ¼ö¤ß¦^µª
§Ú·|¦h¹Á¸Õ¬Ý¬Ý
·PÁ§A

TOP

¦^´_ 4# luhpro


°ÝÃD½Ð±Ð
.Private Sub Worksheet_Calculate()
¼g¦b³o­Ó­pºâµ{§Çªº·N«ä£¿~~¬O¤£¬O´N¬O¥Nªí¥u­n¸ÓÀÉ®×±Ò°Ê~~³o­Ó¤u§@ªí´N¦Û°Ê±Ò°Ê­pºâ
¦Ó³o­Óµ{§Ç¤]¸Ó¼g¦b¤u§@ªí¤¤¦Ó¤£¬O"thisbook"ùؤ]¤£¬O¼g¦b¼Ò²Õ¶Ü~~¹ï¶Ü?

TOP

¦^´_ 6# laieven


   
Sub Worksheet_Calculate()

¬O¤u§@ªíª«¥ó¼Ò²Õªº¹w³]¨Æ¥óµ{§Ç
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¤H¨ÆªºÁ}Ãø»PµZ¿i¡A´N¬O¤@ºØ¦ÒÅç¡C
ªð¦^¦Cªí ¤W¤@¥DÃD