j1221

¦^´_ 39# bubu1019

Hello~

§ï¦¨¨C¬íªº¸Ü¡A¥Î³o­Ó¡G

1. Private Sub change()
   
2.     Dim TimeRange As Range, Rng As Range, R As Range
   
3.     With Sheet8
   
4.         Set TimeRange = .[A:A].Find(TimeSerial(Hour(Time), Minute(Time), Second(Time)), LookIn:=xlFormulas)
   
5.         Set Rng = TimeRange.Offset(, 1).Resize(1, 15)
   
6.     End With
   
7.     Rng.Value = Sheet1.[B8:P8].Value
   
8.       
   
9.     If Time > TimeValue("13:45:00") Then Exit Sub
   
10.     Application.OnTime Now + TimeValue("00:00:01"), "ThisWorkbook.change"

½Æ»s¥N½X

¦ý¬O°O±o¤]­n§â§Aªº®É¶¡±qhh:mm§ï¦¨hh:mm:ss³á¡I

½Ð¸Õ¸Õ¬Ý§a¡I

sjy039

¤p§Ìªì¨Ó¥E¨ì,¤]¦]¬°·Q¶}©l³z¹Ldde,§Q¥Îvbaªºµ{¦¡±N·Q°O¿ýªº©T©w®É¶¡¸ê®Æ¦s¨ú¤U¨Ó°µ¤@¨Ç¹Bºâ
«Ü°ª¿³¦³³o»ò¦h°ª¤â¦b¦¹¼ö¤ß¤À¨É

veign911

§Ú¤]·Q¾Ç­C.¯u·Q¨Ó¸Õ¸Õ.¥i¬O¬Ý¤£¤ÓÀ´!!!!

minghg

¤Ó·PÁÂ.§ä¤F¦n¤[vbaªº¼gªk ¦ýÅv­­¤£¨¬ µLªk¤U¸ü Ä~Äò¾Ç²ß

heavenweaver

§Ú¤]¬OÅv­­¤£¨¬µLªk¤U¸ü¡A¥u¯à¬Ý¬Ý¦b®ÇÃä¸r¼}»PÆ[¼¯¡C

redeemer

¥i±¤ªþ¥óµLªk¤U¸ü¡A¥ý¶iªº­×§ïµLªk°Ñ·Ó¡A¹ê¦b¬O¦³¬Ý¨S¦³À´¡C

barrykuo

³o¸Ì¹ê¦b¤Óºëªö¤F¡A§Ú·Q¾Ç²ß¾Ç²ß¡A·Ç³Æ§Q¥Î¨Ó¨Ï¥Î¨÷°ÓªºRTD ¼Æ¾Ú°O¿ý¦bexcel¸Ì¡A¤£ª¾¹D¥i¥H³o¼Ë¨Ï¥Î¹À¡H

j1221

¦^´_ 10# GBKEE

GBKEE¤j¡A¤£¦n·N«ä¡A¤S¨Ó³Â·Ð§A¡C

¤§«e½Ð¥s¹L±zÃö©ó¨C¤ÀÄÁ°O¿ýDDEªº¼Æ¾Ú¡A²{¦b§Ú·Q­n±N¥¦§ï¦¨¨C5¤ÀÄÁ°O¿ý¤@¦¸¡A©Ò¥H§ó°Ê¤F¤@¤Uµ{¦¡½X¡C

¦ý¬O¥X²{¡u¨S¦³³]©wª«¥óÅܼƩΠWith °Ï¶ôÅÜ¼Æ (¿ù»~ 91)¡vªº°T®§.....
°»¿ù«á¡AÅã¥Ü¦³°ÝÃDªº³¡¥÷¬°¡ySet Rng = TimeRange.Offset(, 1).Resize(1, 11)¡zªº¦a¤è¡A§ÚÀˬd¹L¤§«e¥X°ÝÃDªº¦a¤è¡A¦ý¬O¦ü¥G¸ò¤§«eªº°ÝÃD¤£¤@¼Ë(¤w¸g¤S±z¤§«e´£¨Ñªº¤è¦¡§ï¹L¤F)¡C

¥i§_¦A½Ð±zÀ°§Ú¬Ý¤@¤U©O¡H½Ð¸Ô¨£ªþÀÉ¡C(¸ê®Æ·½¤w¸g§ï¬°¤¸¤jYeswin RTD)·PÁ±z

test.zip (198.79 KB)

¤U¸ü¦¸¼Æ: 11

2014-2-5 13:58

GBKEE

¦^´_ 48# j1221
¦]µ{¦¡ªº FIND ªº®É¶¡ ¤£¤@©w¸¨¦b00,05,10,15....¤ÀÄÁªº®É¶¡¤W
¾É­P¤u§@ªíAÄæFIND¤£¨ì¦Ó³y¦¨¿ù»~

1. Dim xTime As String  '«Å§i¬°¦r¦ê«¬ºA ¶ÈThisWorkbook¼Ò²Õªºµ{§Ç¥i¥Î
   
2. Private Sub Workbook_Open()
   
3.     Sheets("TXF1").[B7:P19000] = ""
   
4.     Sheets("MXF1").[B7:P19000] = ""
   
5.     Sheets("EXF").[B7:P19000] = ""
   
6.     Sheets("FXF").[B7:P19000] = ""
   
7.     Sheets("TWT").[B7:P19000] = ""
   
8.     Sheets("TWO").[B7:P19000] = ""
   
9.     If Time >= TimeValue("08:45:00") And Time <= TimeValue("13:45:00") Then
   
10.         '*** Àç·~®É¶¡¤º ¤£¤@©w¦b 00,05,10,15....ªº¤ÀÄÁ¤W  ******
    
11.         xTime = Minute(Time) - Minute(Time) Mod 5       '¸¨¦b¤W¤@­Ó 5¤ÀÄÁ ,Mod:¨â¼Æ¬Û°£ªº¾l¼Æ
    
12.         xTime = Format(TimeSerial(Hour(Time), xTime, 0), "h:mm:ss")
    
13.         change
    
14.     Else
    
15.         xTime = "08:45:00"
    
16.         Application.OnTime TimeValue(xTime), "ThisWorkbook.change"
    
17.     End If
    
18. End Sub
    
19. Private Sub change()
    
20.     Dim TimeRange As Range, Rng As Range, R As Range
    
21.     ' ***  Time ®É¶¡ÂI¤£¤@©w¸¨¦b5¤ÀÄÁ¤W ********
    
22.     'xTime¬°¸¨¦b 00,05,10,15....ªº¤ÀÄÁ¤Wªº®É¶¡,¥i½T©w¤@©w§ä±o¨ì
    
23.     Set TimeRange = Sheets("TXF1").[A:A].Find(xTime, LookIn:=xlValues)  '§ï¬° ·j´M¤º®e
    
24.     Set Rng = TimeRange.Offset(, 1).Resize(, 11)
    
25.     Rng.Value = Sheets("Main").Range("C9:M9").Value
    
26.    
    
27.     Set TimeRange = Sheets("MXF1").[A:A].Find(xTime) '±µ¤UªºFind·|¨Ì¾Ú¤W¤@¦¸Findªº¿ï¶µ¨Ó·j´M
    
28.     Set Rng = TimeRange.Offset(, 1).Resize(, 11)
    
29.     Rng.Value = Sheets("Main").Range("C11:M11").Value
    
30.    
    
31.     Set TimeRange = Sheets("EXF").[A:A].Find(xTime)
    
32.     Set Rng = TimeRange.Offset(, 1).Resize(, 11)
    
33.     Rng.Value = Sheets("Main").Range("C12:M12").Value
    
34.    
    
35.     Set TimeRange = Sheets("FXF").[A:A].Find(xTime)
    
36.     Set Rng = TimeRange.Offset(, 1).Resize(, 11)
    
37.     Rng.Value = Sheets("Main").Range("C13:M13").Value
    
38.    
    
39.     Set TimeRange = Sheets("TWT").[A:A].Find(xTime)
    
40.     Set Rng = TimeRange.Offset(, 1).Resize(, 17)
    
41.     Rng.Value = Sheets("Main").Range("C2:U2").Value
    
42.    
    
43.     Set TimeRange = Sheets("TWO").[A:A].Find(xTime)
    
44.     Set Rng = TimeRange.Offset(, 1).Resize(, 20)
    
45.     Rng.Value = Sheets("Main").Range("C3:U3").Value
    
46.    
    
47.     If Time > TimeValue("13:45:00") Then Exit Sub
    
48.    
    
49.     xTime = Minute(Time) + 5 - Minute(Time) Mod 5               '¸¨¦b¤U¤@­Ó5¤ÀÄÁ
    
50.     xTime = Format(TimeSerial(Hour(Time), xTime, 0), "h:mm:ss") '¸¨¦b 00,05,10,15....ªº¤ÀÄÁªº®É¶¡¤W
    
51.     ' Now + TimeValue("00:05:00") ->¤£¤@©w¬O¸¨¦b 00,05,10,15....ªº¤ÀÄÁ¤W
    
52.     Application.OnTime TimeValue(xTime), "ThisWorkbook.change"  '½T¹ê¦b¤U¤@­Ó5¤ÀÄÁÂI¤W°õ¦æ
    
53. End Sub

½Æ»s¥N½X

c_c_lai

¦^´_ 48# j1221
¥H "TXF1" ¬°¨Ò¡G

1.     With Sheets("TXF1")
   
2.         Set TimeRange = .[A:A].Find(TimeSerial(Hour(Time), Minute(Time), Second(Time)), LookIn:=xlFormulas)
   
3.         Set Rng = TimeRange.Offset(, 1).Resize(, 11)
   
4.     End With
   
5.     Rng.Value = Sheets("Main").Range("C9:M9").Value

½Æ»s¥N½X

«Øij­×§ï¬°¡G

1.     With Sheets("TXF1")
   
2.         Set TimeRange = .[A:A].Find(TimeSerial(Hour(Time), Minute(Time), Second(Time)), LookIn:=xlFormulas)
   
3.         If Not TimeRange Is Nothing Then
   
4.             TimeRange.Offset(, 1).Resize(, 11) = Sheets("Main").Range("C9:M9").Value
   
5.         End If
   
6.     End With

½Æ»s¥N½X

Range.Find() ·|¸Õ¹Ï§ä¨ì¸Ó¶µ¸ê°Tªº²Ä¤@­ÓÀx¦s®æ¡A¦pªG¨S¦³§ä¨ì²Å¦XªºÀx¦s®æ¡A
«h¦¹¤èªk "¶Ç¦^値" ·|¶Ç¦^ Nothing¡C
·í TimeRange ­È¬° Nothing¡A §A­ì¥»ªº¼gªk«h·|¥X¿ù¡C