[µo°Ý] ½Ð¥ý¶i­Ì¨ó§U°»¿ù

mnsmrtl

1. Sub Pattern(Rng As Range, xCode, xDate As Range)
   
2.     Dim H As Range, L As Range, C As Range, rh As Range, rl As Range
   
3.     Dim Tf1 As Boolean, Tf2 As Boolean
   
4.     Dim i, j, k, th, tl, sw
   
5.     i = 0: j = 0: k = 0
   
6.     th = 0: tl = 0: sw = 0
   
7.     Do While i < 8 '¨ú6­ÓÂI
   
8.         Set H = grabData("°ª", xCode, xDate).Offset(0, j)
   
9.         Set L = grabData("§C", xCode, xDate).Offset(0, j)
   
10.         Set C = grabData("¦¬", xCode, xDate).Offset(0, j)
    
11.         If j > 300 Then Exit Do '­­¨î¸ê®Æ®w½d³ò
    
12.         If C <> "" Then
    
13.             k = k + 1 '­p¼Æ
    
14.             If k = 1 Then GoSub RefreshHL
    
15.             Tf1 = sw >= 0 And C < tl * 0.9
    
16.             Tf2 = sw <= 0 And C > th * 1.1
    
17.             If Tf1 Or Tf2 Then
    
18.                 GoSub MoveAndSwitch
    
19.                 GoSub WriteDataIn
    
20.                 GoSub RefreshHL
    
21.             Else
    
22.                 Set rh = IIf(H > rh, H, rh)
    
23.                 Set rl = IIf(L < rl, L, rl)
    
24.                 th = IIf(H < th, H, th)
    
25.                 tl = IIf(L > tl, L, tl)
    
26.                 GoSub WriteDataIn
    
27.             End If
    
28.         End If        
    
29.         Debug.Print sw & "_" & j & "_" & k & "_" & i
    
30.         j = j + 1
    
31.     Loop
    
32. Exit Sub

½Æ»s¥N½X

¦bdo while°j°é¤º¡Adebug.printªºµ²ªG¡Aj³£¬O±q102¶}©l¡A¤£ª¾¹D¬O­þ¸Ì¿ù¤F
½Ð¤j®aÀ°¦£¬Ý¬Ý
ÁÂÁ¤j®a

a5007185

«Øij§A¥ý¥Î¡uºÊ¬Ý¦¡¡v°lÂÜ¡A
¦]¬°§Aµ{¦¡¸Ì­±ÁÙ¦³¨ä¥Lµ{§Ç°ÑÂø¨ä¤¤¡A
§Ú§â¨ä¥Lµ{§Ç®³±¼«á¨Ã¨S¦³µo²{²§ª¬¡C

mnsmrtl

1. Sub Ac§ìĵ¥Ü()
   
2.     Call GetAlarm(1)
   
3. End Sub
   
4. 
   
5. Sub GetAlarm(Nd)
   
6.     Dim xRow%, xCode, xDate As Range, i, j, writeIn As Range
   
7.     Call AÃö³¬
   
8.     xRow = Sheets("°ò¥»").[A1].End(xlDown).Row
   
9.     For i = 2 To xRow
   
10.         With Sheets("°ò¥»")
    
11.             xCode = .Cells(i, "A")
    
12.             Set writeIn = .Range(.Cells(i, "C"), .Cells(i, "T")) '«¬ºA°ª§C    ¼g¤J¸ê®Æ½d³ò
    
13.         End With
    
14.         Set xDate = Sheets("¦¬").Cells(1, 3).Offset(0, Nd - 1) '®Ú¾Ú°Ñ¼Æ­×§ï°_©l¤Ñ¼Æ
    
15.         Call Pattern(writeIn, xCode, xDate)
    
16.     Next i
    
17.     Call A¶}±Ò
    
18. End Sub
    
19. 
    
20. Function grabData(Sht$, xCode, xDate As Range) '±q¸ê®Æ®w§ì¨ú¼Æ¾Ú
    
21.     Dim matC, matD
    
22.     With Sheets(Sht)
    
23.         matC = WorksheetFunction.Match(xCode, .Columns(1), 0)
    
24.         matD = WorksheetFunction.Match(xDate, .Rows(1), 0)
    
25.         Set grabData = .Cells(matC, matD)
    
26.     End With
    
27. End Function
    
28. 
    
29. Function fbdate(Rng As Range) '«öÀx¦s®æ§ä¡u¤é´Á¡v
    
30.     Set fbdate = Sheets(Rng.Worksheet.Name).Cells(1, Rng.Column)
    
31. End Function
    
32. 
    
33. Sub Pattern(Rng As Range, xCode, xDate As Range)
    
34.     Dim H As Range, L As Range, C As Range, rh As Range, rl As Range
    
35.     Dim Tf1 As Boolean, Tf2 As Boolean
    
36.     Dim i, j, k, th, tl, sw
    
37.     Rng.ClearContents
    
38.     i = 0: j = 0: k = 0
    
39.     th = 0: tl = 0: sw = 0
    
40.     Do While i < 8 '¨ú6­ÓÂI
    
41.         Set H = grabData("°ª", xCode, xDate).Offset(0, j)
    
42.         Set L = grabData("§C", xCode, xDate).Offset(0, j)
    
43.         Set C = grabData("¦¬", xCode, xDate).Offset(0, j)
    
44.         If j > 300 Then Exit Do '­­¨î¸ê®Æ®w½d³ò
    
45.         If C <> "" Then
    
46.             k = k + 1 '­p¼Æ
    
47.             If k = 1 Then GoSub RefreshHL
    
48.             Tf1 = sw >= 0 And C < tl * 0.9
    
49.             Tf2 = sw <= 0 And C > th * 1.1
    
50.             If Tf1 Or Tf2 Then
    
51.                 GoSub MoveAndSwitch
    
52.                 GoSub WriteDataIn
    
53.                 GoSub RefreshHL
    
54.             Else
    
55.                 Set rh = IIf(H > rh, H, rh)
    
56.                 Set rl = IIf(L < rl, L, rl)
    
57.                 th = IIf(H < th, H, th)
    
58.                 tl = IIf(L > tl, L, tl)
    
59.                 GoSub WriteDataIn
    
60.             End If
    
61.         End If
    
62.         Debug.Print H.Column
    
63.         j = j + 1
    
64.     Loop
    
65. Exit Sub
    
66. MoveAndSwitch:
    
67.     i = i + 1
    
68.     sw = IIf(sw >= 0, -1, 1)
    
69.     Return
    
70. WriteDataIn:
    
71.     If i < 7 And i > 0 Then
    
72.         Rng(i) = IIf(sw = 1, rh, rl)
    
73.         Rng(i + 6) = fbdate(IIf(sw = 1, rh, rl))
    
74.         Rng(i + 12) = k
    
75.     End If
    
76.     Return
    
77. RefreshHL:
    
78.     Set rh = H
    
79.     Set rl = L
    
80.     th = H
    
81.     tl = L
    
82.     Return
    
83. End Sub

½Æ»s¥N½X

¦]¬°ÀÉ®×À£ÁY«á¤]¬O¶W¹L1MB¡A©Ò¥H§Ú§â¦³¥Î¨ìªºµ{¦¡½X¶°¤¤©ñ¤@°_¦A¶K¤@¦¸
¹ï©óÅܼÆJ¡A¦b¨ä¥Lµ{§Ç¨S¦³°µ¤°»ò°Ê§@
Àɮפº¡A¬O¦UªÑ²¼ªº¨C¤é¶}½L¥|»ù¤Î¦¨¥æ¶q
¦p¹Ï

¤U¸ü (74.02 KB)

2018-9-3 15:33

ºÊ¬Ý¦¡ªº³¡¤À¡A§Ú­n¦A¬d¤@¤U¸ê®Æ¡A¬Ý«ç»ò¥Î¡KÁÂÁ¼ӤW«üÂI

¤W¶Ç¨ìMEGAªÅ¶¡¤F¡A¤j¬ù15M¡Aºô§}¦p¤U
https://mega.nz/#!uTZkGI4K!XtnUzYtIyzsHBFPNF4hGrcroUtzdO8UthkdFSorPVxk

a5007185

¦^´_ 3# mnsmrtl

ºÊ¬Ý¦¡ªº¤è¦¡¦p¤U¹Ï¡A

¤U¸ü (66.36 KB)

2018-9-3 16:28

¦ý§Ú´ú¸Õ¤U¨Ó¨Ã¨S¦³µo²{¥ô¦ó²§ª¬¡A
§ÚµLªk­«²{§Aªº°ÝÃD¡C

¤U¸ü (24.29 KB)

2018-9-3 16:28

mnsmrtl

¤£ª¾¹D­þ¥X¤F°ÝÃD¡A§Úª©¥»¬O2007¡A¤£ª¾¹D¸ò³o¦³¨S¦³Ãö«Y

¤U¸ü (56.38 KB)

2018-9-3 16:54

a5007185

¦^´_ 5# mnsmrtl

ºÊ¬Ý¬O°w¹ïÅܼưµºÊ¬Ý¡A
©Ò¥H¦pªG§A·Q­nºÊ¬Ý§A¹Ï¤¤ªº°T®§ªº¸Ü¡A
§A»Ý­n§âDebug.Print ¥h±¼

mnsmrtl

¦^´_ 6# a5007185

¸g­×§ï«á¡A§Úµo²{µ{¦¡¦³°õ¦æ¡A¦ý¬Odebug.Print¥¼Åã¥Ü
¤£ª¾¹D¬O­þ¥X¤F°ÝÃD¡AÁÂÁ¼ӤW«ü¾É

a5007185

¦^´_ 7# mnsmrtl


­ìµ{¦¡½X¤£»Ý­n§ó°Ê¡A
§Úªº·N«ä¬O»¡§A¦b¶i¦æºÊ¬Ýªº®É­Ô¡A
»Ý­n§âDebug.Print¥h±¼¡C

¤U¸ü (202.66 KB)

2018-9-4 14:16

mnsmrtl

¦^´_ 8# a5007185

«z®@¡AÁÙÅý±z°µ¼v¤ù¡A¯u¤£¦n·N«ä
ºÊ¬Ý¦¡°ÝÃD¤w¸Ñ¨M¡A¤£¹L¦b§Y®É¹Bºâµøµ¡¤º¡A¨ÌµM¥uÅã¥Ü102¤§«á
¦ý¼g¤JÀx¦s®æªº³¡¤À¡A½T¹ê¦³100¥H¤ºªº
¥i¯à¬OÅã¥Ü°ÝÃD¡A©Î¬O°O¾ÐÅ餣¨¬§a¡K¡H

ºô¸ô¤Wªº¤å³¹¬O¦³´£¹L¡A¦³¥i¯à¦]¬°­È¤£¦s¦b¡A©Ò¥H¨SÅã¥Ü¥X¨Ó
©Î³\¬O¨ä¤¤¤@­ÓÅܼƨì102¤§«á¤~¦³¡H

Á`¤§¡A¤ñ¹ïÀx¦s®æªº¤º®e¤§«á¡A§Úµo²{µ{§ÇªºÅÞ¿è¤]¤£¤Ó¹ï¡A©Ò¥H¾ã­Ó³£­×§ï¤F
¥Ø«e¤w¨S¦³°ÝÃD¡AÁÂÁ±zªºÀ°§U¡A·P®¦

a5007185

¦^´_ 9# mnsmrtl

§Ú·Q§Aµ{¦¡À³¸Ó³£¬O¥¿±`¹B§@ªº¡A
¥u¤£¹L¦]¬°¡u§Y®É¹Bºâ¡v®e¯Ç¤£¤F³o»ò¦h¸ê®Æ¡A
©Ò¥H§â¤@³¡¤À¸ê®Æ¦Y±¼¤F¡C

¤U¸ü (54.92 KB)

2018-9-5 15:32

§Ú¼g¤F²©öªº°j°é¡A
Åý¥L¦R¥X250¦æ¸ê®Æ¡A
©Ô¨ì³»¥u³Ñ¤U52¦æ¡A
§Úªº±À´ú¬O¡u§Y®É¹Bºâ¡v³Ì¦h¯à°÷®e¯Ç200¦æ¸ê®Æ¡A
­è­èªº´ú¸Õ·í¤¤¥[¤W©³¤UªÅ¥Õ2¦æ¡A«ê¦n´N¬O200¦æ¡A
¥H¤Wµ¹§A°µ­Ó°Ñ¦Ò¡C

¥t¥~«Øij§A¥i¥H§Q¥ÎF8ªº¥\¯à³v¨B°õ¦æµ{¦¡¦b·f°t¡uºÊ¬Ý¦¡¡v¡A
³o¼Ë§A´N¥i¥H¤F¸Ñ¾ã­Óµ{¦¡¨C­ÓÅܼƪº¹B§@¤F¡C
§Æ±æ¥H¤W¥i¥HÀ°§U¨ì§A¡C