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

¬ö¿ýªÑ²¼¶}½L³æ¶q­È

¬ö¿ýªÑ²¼¶}½L³æ¶q­È

¥»©«³Ì«á¥Ñ GBKEE ©ó 2015-9-16 07:05 ½s¿è

¦U¦ìª©¤Í¦n¡A
¦]¦b¼gExcel vba®É¡A¹J¨ì¤@­Óµ{¦¡¤£ª¾¦p¦ó§¹¦¨¡A¦p¤U
1.¥Øªº¦b©ó­n°O¿ý­ÓªÑªÑ²¼ªº¶}½L¶qªº­È¡A¤]´N¬O»¡±NDDE IÄæ²Ä¤@µ§Åܤƪº­È°O¿ý¨ìHÄ椤¡A¤§«á´N¤£ÀHIÄæÅܤơC
2.¦]ªÑ²¼¦³10-20ÀÉ¥ª¥k¡AHÄ欰¦U¦ÛÅܤƪº²Ä¤@µ§¶q¡C

½Ð¤j¤j´£¨Ñ¬ÛÃöªºµ{¦¡¡Aµ¹¤p§Ì§Ú·í§@¤@­Ó½d¥»!!

testing.rar (27.4 KB)

°Ñ·ÓÀÉ

Kenny

¦^´_ 1# ken2192
¸Õ¸Õ¬Ý
  1. '¦Û¿ïªÑ ¤u§@ªí¼Ò²Õ
  2. Option Explicit
  3. Private Sub Worksheet_Calculate()
  4.     Dim Rng As Range, E As Range
  5.     Set Rng = Range("A2", Range("A2").End(xlDown)).Columns("H") 'HÄæ(²Ä¤@µ§¶q)ªº¦ì¸m
  6.     If Time < #9:00:00 AM# Then
  7.         Rng = ""      '²MªÅHÄ檺²Ä¤@µ§¶q
  8.     ElseIf Time >= #9:00:00 AM# And Rng.Count <> Application.Count(Rng) Then
  9.          'HÄ檺­Ó¼Æ<>HÄ榳¼Æ¦rªº­Ó¼Æ,ÁÙ¦³ªÑ²¼²Ä¤@µ§¶q¨S¦³¿é¤J
  10.         For Each E In Rng.Cells
  11.             If E = "" And E.Cells(1, 2) > 0 Then E = E.Cells(1, 2)
  12.         Next
  13.     End If
  14. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

·P®¦¤£ºÉ¡A¦ý¸g¹L´ú¸Õ¡A09:00¶}½L«á¡AHÄæ¨Ã¤£·|¦Û°Ê¶]¥X¼Æ¦r¥X¨Ó!!
¥H¤U§Ú¦b¸Ô­z§Ú»Ý­nªº¥\¯à¡A¦]¦bºô¸ô©M¦¹°Q½×ª©¨Ã¨S¦³¬Ý¹LÃþ¦üªºvba¡A©Ò¥H¨S¦³¥i¥H°Ñ¦Òªºµ{¦¡¡A½Ðª©¥D¬O§_¥i¥H¨Ì·Ó¤U¦Cªº´y­z¼g¥X²³æ²Å¦Xªºvba¡A·P®¦¤£ºÉ!!
1.¥Øªº¦b©ó­n°O¿ý­ÓªÑªÑ²¼ªº¶}½L¶qªº­È¡A¤]´N¬O»¡±NDDE "IÄæ"(I9¥H¤UªºªÑ²¼)²Ä¤@µ§Åܤƪº­È°O¿ý¨ì"HÄæ"¤¤¡A¤§«á´N¤£ÀH"IÄæ"ÅܤơC
2."IÄæ"¦b¶}½L«e³£¬O¬°¹s¡A9:00¤§«á·|¶}©l¸õ°Ê¡C¦ý¨C¤äªÑ²¼¶}©l¸õ°Êªº®É¶¡¨Ã¤£¤@¼Ë¡C¦pI9¸õ°Ê®É¶¡¥i¯à¬O09:05¡AI10¸õ°Êªº®É¶¡¬°09:18¤§Ãþªº¡A¦U¦Û¨Ì·Ó¤£¦P®É¶¡ªº²Ä¤@µ§¶q°O¿ý¦bHÄæ¦ì¤¤¡C

¥H¤U¬O§Ú±q¬Ý½L³nÅé³sµ²¨ìexcelªºÀɮסA½Ð°Ñ¦Ò!!
­ÓªÑ§Y®ÉºÊ±±¬ö¿ý-20150923.rar (110.19 KB)
Kenny

TOP

¦^´_ 3# ken2192

§A´ú¸Õ¬Ý¬Ý
   Ex.rar (9.33 KB)
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

Worksheets("DDE").Range("j1")=""
If Worksheets("DDE").Range("j1")<> Worksheets("DDE").Range("i1") and
Worksheets("DDE").Range("j1")>0 Then '¥H j1 ¦¨¥æ¶q§PÂ_¬O§_¦³¥æ©ö
Worksheets("DDE").Range("j1")= Worksheets("DDE").Range("i1")
end if
' Worksheets("DDE").Range("i1") ¸ò DDE ¸ê°T·½³sµ²

TOP

·P®¦GBKEE ª©¥D¡A³Ìªñ¤ÏÂдú¸Õ´X¦¸¡A¨ÌµMµLªk¦b09:00¶}½L«á¦Û°Ê¬ö¿ý¶}½L¶q¤]¤£·|¦b09:00¤§«e²M°£¬ö¿ýÄæ¦ì¡A¦ý¦pªG¬O¤â°Ê±Ò°Êªº¸Ü´N·|¦³­È¡A¥H¤U¬O§Ú®M¥Î¦b§Úªºexcel¤Wªºµ{¦¡¡A¬O§_°ÝÃD¬O¥X¦b­n¼g¤J¦Û°Ê±Ò°Êµ{¦¡ªºfunction!!

Option Explicit
'¦Û¿ïªÑ ¤u§@ªí¼Ò²Õ
Private Sub AWorksheet_Calculate()
    Dim Rng As Range, E As Range
    On Error Resume Next
    If IsError([A9]) Then Exit Sub  'Àɮ׶}±Ò®ÉDEE·|¶Ç¦^ @N/A
    If Time < #9:00:00 AM# Then
        Set Rng = UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
        If Not Rng Is Nothing Then Debug.Print Time, Not Rng Is Nothing
            With Range("A9", Range("A9").End(xlDown))
                .Columns("K") = ""    ''²MªÅHÄ檺²Ä¤@µ§¶q
            End With
        Exit Sub
    End If
    Set Rng = Range("A9", Range("A9").End(xlDown)).Columns("K") 'KÄæ(²Ä¤@µ§¶q)ªº¦ì¸m
    If Time >= #9:00:00 AM# And Rng.Count <> Application.Count(Rng) Then
       '  KÄ檺­Ó¼Æ<>KÄ榳¼Æ¦rªº­Ó¼Æ,ÁÙ¦³ªÑ²¼²Ä¤@µ§¶q¨S¦³¿é¤J
        Application.EnableEvents = False
        For Each E In Rng.Cells
            If E = "" And E.Cells(1, 2) > 0 Then
                E = E.Cells(1, 2)
        End If
        Next
        Application.EnableEvents = True
    End If
End Sub
Kenny

TOP

¦^´_ 6# ken2192

©êºpªþÀɤF¤@­ÓA
  1. '¦Û¿ïªÑ ¤u§@ªí¼Ò²Õ
  2. Private Sub Worksheet_Calculate()
½Æ»s¥N½X
½Ð­×§ï¦A¸Õ¸Õ¬Ý
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

·P®¦GBKEE ¡A¤µ¤Ñ¦­¤W´ú¸Õµ²ªG¬O¦³¦Û°Ê¶]¥X¶}½L¶q¥X¨Ó¡A¦ý¥u¦³¤@ÀɪѲ¼¡A¤]´N¬O9ÂI¶}½L«á³Ì§ÖÅã¥Ü¥X¶}½L¶qªºªÑ²¼(©ó09:00:01, 3673 109±i)¡A¨ä¾lÀɪºªÑ²¼¨Ã¨S¦³¨Ì§Ç¶]¥X¸Ó¶}½L¶q¡A¦]¬°¨ä¥L¶}½L¶q³£¦b09:00:01«á¤~¥X²{¡A¬O§_°ÝÃD¥X¦b­n­×§ï¦¨¿W¥ß¨Æ¥ó?
Kenny

TOP

        ÀR«ä¦Û¦b : ¥@¤W¦³¨â¥ó¨Æ¤£¯àµ¥¡G¤@¡B§µ¶¶ ¤G¡B¦æµ½¡C
ªð¦^¦Cªí ¤W¤@¥DÃD