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

[µo°Ý] ½Ð°Ý¦p¦ó±N¥H³s±µEXCELªºDDE¼Æ¾Ú¨C¤ÀÄÁ¬ö¿ý¤U¨Ó¡H

¦^´_ 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
§Ú·R³Â»¶®a±Ú°Q½×°Ï¡I¡I

TOP

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

TOP

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

TOP

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

TOP

§Ú¤]¬OÅv­­¤£¨¬µLªk¤U¸ü¡A¥u¯à¬Ý¬Ý¦b®ÇÃä¸r¼}»PÆ[¼¯¡C
«Ü©êºp¡A§Ú¬O¤p¾Ç¥Í¡A¤£¯à¤U¸üÀɮסA¬O­Ó¤p§¾«Ä¡I

TOP

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

TOP

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

TOP

¦^´_ 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)
§Ú·R³Â»¶®a±Ú°Q½×°Ï¡I¡I

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-2-5 16:58 ½s¿è

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

TOP

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