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

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

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

¥»©«³Ì«á¥Ñ cdkee ©ó 2012-5-1 22:19 ½s¿è

½Ð±Ð¤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)

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

TOP

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

TOP

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

TOP

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

TOP

¦^´_ 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. Next
  21. .[A1].Resize(s, 7) = Application.Transpose(Application.Transpose(Ar))
  22. End With
  23. .Save '¦sÀÉ
  24. End With
  25. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ cdkee ©ó 2012-4-30 03:25 ½s¿è

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

TOP

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

TOP

¦^´_ 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¦¨­«½Æ¶}±ÒÀɮתº¿ù»~
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# cdkee
ªþÀÉ ¸Õ¸Õ¬Ý
¦Û°Ê¿z¿ï.rar (37.32 KB)
  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

TOP

        ÀR«ä¦Û¦b : ¥@¤W¦³¨â¥ó¨Æ¤£¯àµ¥¡G¤@¡B§µ¶¶ ¤G¡B¦æµ½¡C
ªð¦^¦Cªí ¤W¤@¥DÃD