- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-5-12 10:52 ½s¿è
¦^´_ 9# ko310kmo
If TheTime <> TimeSerial(Hour(Time), Minute(Time), 0) Then
Àɮפ@°õ¦æ®É TheTime <> TimeSerial(Hour(Time), Minute(Time), 0)
°õ¦æ¤§«á¦³³]©w TheTime = TimeSerial(Hour(Time), Minute(Time), 0)
¸g®É¶¡¨ì¤U¤@¤ÀÄÁ«á TheTime <> TimeSerial(Hour(Time), Minute(Time), 0)
ReDim AR(2 To Range("b2").End(xlDown).Row, 1 To 6)
±N AR°}¦C «·sªì©l¤Æ (Âk¹s)
.Offset(1, 3).Resize(1, 6) = Application.Index(AR, E.Row - 1) 'Ū¨ú¤@¤ÀÄÁ¤ºªº¸ê®Æ
AR°}¦C¬O¬ö¿ý¤@¤ÀÄÁ¤º¦U¼i¬ù»ùªº ®É¶¡, ¶}, °ª, §C, ¦¬, ¶q
½Ð°Ý[B2]¬O¤°»ò·N«ä
http://forum.twbts.com/viewthread.php?tid=2811&from=favorites
*******************************************************
«·sקï¸Õ¸Õ¬Ý ¦ý»P§Aªº´Á±æ©|¦³¶ZÂ÷- Private Sub Worksheet_Calculate()
- Dim E, i%
- On Error GoTo t '³B²z §Y®É¦¨¥æ»ù»P¦¨³æ¶q ½d³ò¨S¦³¿ù»~È
- If Not Range("C2", Range("D2").End(xlDown)).SpecialCells(xlCellTypeFormulas, xlErrors) Is Nothing Then
- Exit Sub '¦¹Àɮתì¶}©l³sµ²®É§Y±Ò°Ê«ºâ ->¦ý¬O©|¦³¥¼³sµ²¨ìªºÀx¦s®æ·|¶Ç¦^¿ù»~È
- End If
- t:
- A = Application.Transpose(Range("C2", Range("D2").End(xlDown)).Value) '§Y®É¦¨¥æ»ù»P¦¨³æ¶q½d³ò°}¦C
- If TheTime <> TimeSerial(Hour(Time), Minute(Time), 0) Then '³]©w¨C¤ÀÄÁ¾ã
- ReDim AR(2 To Range("b2").End(xlDown).Row, 1 To 6) '°}¦C «·sªì©l¤Æ (Âk¹s)
- ¶}½L»ù = Empty 'Åܼƪì©l¤Æ (Âk¹s)
- Ex
- If Not IsEmpty(B) Then
- For Each E In Range("b2", Range("b2").End(xlDown))
- Sheets(E.Text).Cells(Rows.Count, "j").End(xlUp).Offset(, 6) = B(1, E.Row - 1) '¤@¤ÀÄÁµ²§ô®Éªº»ù®æ
- Next
- End If
- TheTime = TimeSerial(Hour(Time), Minute(Time), 0)
- Else
- Ex
- End If
- If IsEmpty(B) Then '¤ñ¹ï°}¦C¬°¸ê®Æ
- For Each E In Range("b2", Range("b2").End(xlDown))
- With Sheets(E.Text).Cells(Rows.Count, "j").End(xlUp)
- .Offset(1) = E(1, 2) '°O¿ý¤U ¦¨¥æ»ù
- .Offset(1, 1) = E(1, 3) '°O¿ý¤U ³æ¶q
- .Offset(1, 2).Resize(1, 6) = Application.Index(AR, E.Row - 1) 'Ū¨ú¤@¤ÀÄÁ¤ºªº¸ê®Æ
- End With
- Next
- Else
- For i = 1 To UBound(A, 2)
- If A(1, i) <> B(1, i) Or A(2, i) <> B(2, i) Then ' ¼i¬ù»ùªº(¦¨¥æ»ù©Î¦¨³æ¶q)¦³ÅÜ°Ê
- With Sheets([B2].Cells(i, 1).Text).Cells(Rows.Count, "j").End(xlUp)
- .Offset(1) = [B2].Cells(i, 2) '°O¿ý¤U §Y®É¦¨¥æ»ù
- .Offset(1, 1) = [B2].Cells(i, 3) '°O¿ý¤U ³æ¶q
- .Offset(1, 2).Resize(1, 6) = Application.Index(AR, i) 'Ū¨ú¤@¤ÀÄÁ¤ºªº¸ê®Æ
- End With
- End If
- Next
- End If
- B = A
- End Sub
- Sub Ex() 'Sheet1ªºµ{¦¡½X §Aµo°Ýªº¨º½g [dde tick¸ê®ÆÂà´«¤ÀÄÁ¸ê®Æ]
- Dim i%
- If IsEmpty(¶}½L»ù) Then
- ReDim ¶}½L»ù(2 To UBound(AR))
- For i = 2 To [B1].End(xlDown).Row
- ¶}½L»ù(i) = Cells(i, "C")
- Next
- End If
- For i = 2 To [B1].End(xlDown).Row
- If TheTime <> TimeSerial(Hour(Time), Minute(Time), 0) Then
- AR(i, 1) = Time 'TimeSerial(Hour(Time), Minute(Time), 0) '®É¶¡
- AR(i, 2) = ¶}½L»ù(i) '¨C¤ÀÄÁ¶}½L»ù
- AR(i, 3) = Cells(i, "C") '¨C¤ÀÄÁªº³Ì°ª»ù
- AR(i, 4) = Cells(i, "C") '¨C¤ÀÄÁªº³Ì§C»ù
- AR(i, 6) = Cells(i, "D") '¨C¤ÀÄÁªº¦¨¥æ¶q
- Else
- AR(i, 1) = Time
- AR(i, 3) = IIf(Cells(i, "C") > AR(i, 3), Cells(i, "C"), AR(i, 3)) '¨C¤ÀÄÁªº³Ì°ª»ù
- AR(i, 4) = IIf(Cells(i, "C") < AR(i, 4), Cells(i, "C"), AR(i, 4)) '¨C¤ÀÄÁªº³Ì§C»ù
- 'AR(i, 5) = Cells(i, "C") '¨C¤ÀÄÁªº¦¬½L»ù
- AR(i, 6) = AR(i, 6) + Cells(i, "D") '¨C¤ÀÄÁªº¦¨¥æ¶q
- End If
- Next
- End Sub
½Æ»s¥N½X |
|