[µo°Ý] ¦b¨C­Ó«ü©wªº®É¶¡´¡¤J¬ÛÃö¼Æ¾Ú(¤w¸Ñ¨M)

cdkee

½Ð±Ð¤j¤j¡A¦p¦ó¦bSHEET1¤¤¥ª¤âÃ䪺¼Æ¾Ú¡A­n¦b¨C¤Ñ¨C­Ó9:16:000©M13:31:000¡A¦b¨ä¤W´¡¤J¨â¦æ¼Æ¾Ú¡A±Nµ²ªGÅã¥Ü¦b¥kÃä¡C
¤£ª¾«ç»ò»¡¡A®ÄªG´N¦pSHEET1¤¤¥k¤âÃäÅã¥Üªº¼Æ¾Ú¡A´¡¤J¨â¦æªº¼Æ¾Ú(ºñ¦â)¬O»P¥ª¤âÃ䪺¬ÛÃö¼Æ¾Ú(ºñ¦â)¬Û¦P¡C
ÁÂÁÂ!
TEST.rar (85.26 KB)

¤U¸ü¦¸¼Æ: 25

2012-4-29 04:45

cdkee

¬O§_§Úªº°µªk¤£¦æ¡A·Ð½Ð±Ð¦U¦ì¤j¤j«ü±Ð¡A·PÁÂ!

GBKEE

¦^´_ 2# cdkee
  9:16:000©M13:31:000   ³o®æ¦¡¦h¤@­Ó0

register313

¦^´_ 1# cdkee
°Ñ¦Ò,¸ê®Æ¶q¤j®É³t«×ºC

1. Sub aa()
   
2. [A:G].Copy [J1]
   
3. For K = [K1].End(xlDown).Row To 1 Step -1
   
4.   T = Cells(K, "K")
   
5.   If T = "9:16:000" Or T = "13:31:000" Then
   
6.     Cells(K, "J").Resize(1, 7).Insert Shift:=xlDown
   
7.     Cells(K, "J").Resize(1, 7).Insert Shift:=xlDown
   
8.     Cells(K, "J").Resize(2, 1) = Cells(K + 2, "J")
   
9.     Cells(K, "L").Resize(2, 5) = Cells(K + 2, "L")
   
10.     Select Case T
    
11.       Case "9:16:000"
    
12.         Cells(K, "K") = "9:14:000"
    
13.         Cells(K + 1, "K") = "9:15:000"
    
14.       Case "13:31:000"
    
15.         Cells(K, "K") = "13:29:000"
    
16.         Cells(K + 1, "K") = "13:30:000"
    
17.     End Select
    
18.   End If
    
19. Next K
    
20. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 2# cdkee

1. Option Explicit
   
2. Sub Ex_Replace()   '­×§ï :000   ¬° :00
   
3.     With ActiveSheet.Range("B:B,K:K")
   
4.         .Replace ":000", ":00", xlPart
   
5.         .NumberFormatLocal = "h:mm:ss;@"
   
6.     End With
   
7. End Sub
   
8. Sub Ex()
   
9.     Dim xi As Date, Ea, Rng(1 To 3) As Range
   
10.     On Error Resume Next
    
11.     Application.ScreenUpdating = False
    
12.     With Sheet1
    
13.         .AutoFilterMode = False
    
14.         Set Rng(1) = .Range("J1").CurrentRegion
    
15.         Set Rng(1) = .Range(Rng(1)(2, 1), Rng(1)(Rng(1).Rows.Count, Rng(1).Columns.Count))
    
16.         For xi = DateValue(.[J2]) To DateValue(.[J2].End(xlDown))
    
17.             For Each Ea In Array("9:16:00", "13:31:00")
    
18.                 .AutoFilterMode = False
    
19.                 .Range("J1").AutoFilter 1, Format(xi, "MM/DD/yYYY")
    
20.                 .Range("J1").AutoFilter 2, Ea
    
21.                 Set Rng(2) = Rng(1).SpecialCells(xlCellTypeVisible)   '¿z¿ï¤£¨ì¸ê®Æ :¦³¿ù»~
    
22.                 If Err.Number = 0 Then
    
23.                     .AutoFilterMode = False
    
24.                     Rng(2).Resize(2).Insert
    
25.                     With Rng(2).Offset(-2).Resize(2)
    
26.                         .Interior.ColorIndex = 6
    
27.                         .Columns(1).Value = Rng(2).Cells(1)
    
28.                         .Cells(1, 2) = Rng(2).Cells(2) - #12:02:00 AM#
    
29.                         .Cells(2, 2) = Rng(2).Cells(2) - #12:01:00 AM#
    
30.                         .Columns("C:F") = Rng(2).Cells(3).Value
    
31.                     End With
    
32.                 End If
    
33.             Next
    
34.         Next
    
35.     End With
    
36.     Application.ScreenUpdating = True
    
37. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 1# cdkee
¸ê®ÆÂл\

1. Sub Ex()
   
2. Dim A As Range, Rng As Range, Ar(), t As Date
   
3. fs = ThisWorkbook.Path & "\TEST.xlsx" '­n³B²zªºÀÉ®×
   
4. With Workbooks.Open(fs)
   
5. With .Sheets(1)
   
6. For Each A In .Range(.[B1], .[B1].End(xlDown))
   
7. If Format(A, "hh:mm:ss") = "09:16:00" Or Format(A, "hh:mm:ss") = "13:31:00" Then
   
8. k = A.Offset(, 1): t = CDate(Format(A, "hh:mm:ss")) - TimeValue("00:02:00")
   
9.    For i = 1 To 2
   
10.        ReDim Preserve Ar(s)
    
11.        X = Format(t, "h:mm:ss0")
    
12.        Ar(s) = Array(A.Offset(, -1), X, k, k, k, k, "")
    
13.        s = s + 1
    
14.     t = t + TimeValue("00:01:00")
    
15.   Next
    
16. End If
    
17.    ReDim Preserve Ar(s)
    
18.    Ar(s) = Array(A.Offset(, -1).Value, A.Value, A.Offset(, 1).Value, A.Offset(, 2).Value, A.Offset(, 3).Value, A.Offset(, 4).Value, A.Offset(, 5).Value)
    
19.    s = s + 1
    
20. 
    
21. Next
    
22. .[A1].Resize(s, 7) = Application.Transpose(Application.Transpose(Ar))
    
23. End With
    
24. .Save '¦sÀÉ
    
25. End With
    
26. End Sub

½Æ»s¥N½X

cdkee

¥ýÁÂÁ¤T¦ì¤j¤j¥Î¤ß«ü¾É!
¸Õ¤F¤T¦ì¤j¤jªº¤½¦¡¡AÁÙ¬O¶]¤£¥X¨Ó¡A¬O§Ú§ï¿ù¤F¤°»ò¡AÁٽФj¤j¦A«ü±Ð¡AÁÂÁÂ!
register313.rar (85.24 KB)

¤U¸ü¦¸¼Æ: 3

2012-4-30 01:16

GBKEE.rar (86.28 KB)

¤U¸ü¦¸¼Æ: 7

2012-4-30 01:16

Hsieh.rar (85.54 KB)

¤U¸ü¦¸¼Æ: 6

2012-4-30 01:16

register313

¦^´_ 7# cdkee
¤@¯ë¼Ò²Õ­n¥[¦bModule¤º
¬¡­¶Ã¯¼Ò²Õ¤~¥[¦bThisWorkbook¤º
¤u§@ªí¼Ò²Õ«h¥[¦bSheet¤º
register313.rar (53.89 KB)

¤U¸ü¦¸¼Æ: 5

2012-4-30 07:56

Hsieh

¦^´_ 7# cdkee
¤@¼ÓªºÀ£ÁYÀɦ³2­ÓEXCELÀɮסA§Ú¦b·Q§A¬O­n¥HTEST.xlsm¤¤ªº¥¨¶°¨Ó¶}±ÒTEST.xlsxÀÉ®×
µM«á­×§ïTEST.xlsxªº¤º®e¡A©Ò¥H¥[¤W¤F¶}±ÒÀɮתº°Ê§@
¦pªG§Aªºµ{¦¡½X©ñ¦bTEST.xlsxªº¥ô¦ó¼Ò²Õ¤º¡A³£¤@¼Ë³y¦¨­«½Æ¶}±ÒÀɮתº¿ù»~

GBKEE

¦^´_ 7# cdkee
ªþÀÉ ¸Õ¸Õ¬Ý
¦Û°Ê¿z¿ï.rar (37.32 KB)

¤U¸ü¦¸¼Æ: 6

2012-4-30 10:10

1. Sub Ex()
   
2.     Dim xi As Date, Ea, Rng(1 To 3) As Range
   
3.     On Error Resume Next                                        'µ{¦¡¦³¿ù»~Ä~Äò°õ¦æµ{¦¡
   
4.     Application.ScreenUpdating = False                          '¿Ã¹õ§ó·s:¨ú®ø
   
5.     With Sheet1
   
6.         .AutoFilterMode = False
   
7.         ' (False) ¨ú®ø[¦Û°Ê¿z¿ï]¤U©Ô½b¸¹ : ¦p¥ý«e¦³[¦Û°Ê¿z¿ï]¹L ·|¤ÞÅT¦A¦¸ªº¥t³]·Ç«h«áªº¿z¿ï¸ê®Æ½d³ò
   
8.         Set Rng(1) = .Range("a1").CurrentRegion
   
9.         Set Rng(1) = .Range(Rng(1)(2, 1), Rng(1)(Rng(1).Rows.Count, Rng(1).Columns.Count))
   
10.         .[A1].AutoFilter Field:=2, Criteria1:="=9:16:00", Operator:=xlOr, Criteria2:="=13:31:00"
    
11.                                                                 '¦Û°Ê¿z¿ï :²Ä2Äæ ³]¤U·Ç«h
    
12.         Set Rng(2) = Rng(1).SpecialCells(xlCellTypeVisible)     '¨Ì·Ç«h [¦Û°Ê¿z¿ï] ¨ìªº¸ê®Æ°Ï:¦p¨S¦³¸ê®Æ    ·|¦³¿ù»~
    
13.         .AutoFilterMode = False                                 '¨ú®ø[¦Û°Ê¿z¿ï]  Åã¥Ü©Ò¦³¸ê®Æ
    
14.         For Each Ea In Rng(2).Rows                              '³B¸Ì¨C¤@¦C(¨Ì·Ç«h [¦Û°Ê¿z¿ï] ¨ìªº¸ê®Æ°Ï)
    
15.             Ea.Resize(2).Insert                                 'Insert (´¡¤J) : [¦Û°Ê¿z¿ï]¦p¤£ Åã¥Ü©Ò¦³¸ê®Æ ·|¦³¿ù»~
    
16.             With Ea.Offset(-2).Resize(2)
    
17.                 .Interior.ColorIndex = 6
    
18.                 .Columns(1).Value = Rng(2).Cells(1)
    
19.                 .Cells(1, 2) = Rng(2).Cells(2) - #12:02:00 AM#
    
20.                 .Cells(2, 2) = Rng(2).Cells(2) - #12:01:00 AM#
    
21.                 .Columns("C:F") = Rng(2).Cells(3).Value
    
22.              End With
    
23.         Next
    
24.     End With
    
25.     Application.ScreenUpdating = True                            ' ¿Ã¹õ§ó·s:«ì´_
    
26.     If Err.Number <> 0 Then MsgBox "§ä¤£¨ì¸ê®Æ"
    
27. End Sub

½Æ»s¥N½X