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

[µo°Ý] ¸ê®Æ¤ÀÃþ

[µo°Ý] ¸ê®Æ¤ÀÃþ

ÀɮפºSheet1¸ê®Æ®æ¦¡,­n¤ÀÃþ¬°Sheet2¤ºªºÅã¥Ü¤è¦¡,¨Ã­pºâ¥X³Ñ¾lªº®É¶¡
½Ð°ÝVBµ{¦¡­n¦pªG¼¶¼g,ÁÂÁÂ

¸ê®Æ¤ÀÃþ.zip (2.78 KB)

¦³¤@ÂI§Ñ¤F´£¨ì
Sheet2¤º¬OªÅ¥Õªº,¨C­Ó¶µ¥Ø¬O­n±qSheet1 Copy¶i¨Ó

TOP

¦^´_ 2# jcchiang
  1. Sub ex()
  2. Dim Ay(), Ary(), A As Range, C As Range
  3. With Sheet1
  4. For Each A In .Range(.[B4], .[IV4].End(xlToLeft)).SpecialCells(xlCellTypeConstants)
  5.     For Each C In .Range(.[A5], .[A5].End(xlDown))
  6.       ar = Split(C, "-")
  7.       ReDim Ay(UBound(ar) + 4)
  8.       Ay(0) = A: Ay(1) = ar(0): Ay(2) = Left(ar(1), InStr(ar(1), "(") - 1)
  9.       If IsDate(.Cells(C.Row, A.Column)) Then
  10.          Ay(3) = .Cells(C.Row, A.Column): Ay(4) = .Cells(C.Row, A.Column + 1)
  11.          Ay(5) = Ay(4) - Date
  12.          Else
  13.          Ay(3) = Evaluate(Replace(.Cells(C.Row, A.Column), "~", "+")) / 2
  14.          Ay(4) = .Cells(C.Row, A.Column + 1): Ay(5) = Ay(3) - Ay(4)
  15.       End If
  16.     ReDim Preserve Ary(s)
  17.     Ary(s) = Ay
  18.     Erase Ay: s = s + 1
  19.     Next
  20. Next
  21. End With
  22. Sheet2.UsedRange.Offset(2) = ""
  23. Sheet2.[A3].Resize(s, 6) = Application.Transpose(Application.Transpose(Ary))
  24. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 3# Hsieh


    ·PÁª©¤j«ü¾É
       ½Ð°Ý¦pªG¦WºÙ¤¤µL"-"¬O§_¬O¦bar = Split(C, "-")¸Ì¦h¥[¤@­Ó§P§O
       ¨º¦pªG«O¾i¤é´Á¤º¬°"---",¥B¤£±N¸ê®Æ§ì¥X,¬O§_¬O¦b¤U¦Cµ{¦¡¤º¦h¥[¤@­ÓElse§P§O¸ê®Æ
    If IsDate(.Cells(C.Row, A.Column)) Then
         Ay(3) = .Cells(C.Row, A.Column): Ay(4) = .Cells(C.Row, A.Column + 1)
         Ay(5) = Ay(4) - Date
         Else
         Ay(3) = Evaluate(Replace(.Cells(C.Row, A.Column), "~", "+")) / 2
         Ay(4) = .Cells(C.Row, A.Column + 1): Ay(5) = Ay(3) - Ay(4)
      End If
¸ê®Æ¤ÀÃþ.zip (5.52 KB)

TOP

¦^´_ 4# jcchiang
  1. Sub ex()
  2. Dim Ay(), Ary(), A As Range, C As Range
  3. With Sheet1
  4. For Each A In .Range(.[B4], .[IV4].End(xlToLeft)).SpecialCells(xlCellTypeConstants)
  5.     For Each C In .Range(.[A5], .[A5].End(xlDown))
  6.       If InStr(C, "-") > 0 Then
  7.         ar = Split(C, "-")
  8.         Else
  9.         ar = Split(C & "-(", "-")
  10.       End If
  11.       ReDim Ay(UBound(ar) + 4)
  12.       Ay(0) = A: Ay(1) = ar(0): Ay(2) = Left(ar(1), InStr(ar(1), "(") - 1)
  13.       If IsDate(.Cells(C.Row, A.Column)) Then
  14.          Ay(3) = .Cells(C.Row, A.Column): Ay(4) = .Cells(C.Row, A.Column + 1)
  15.          Ay(5) = Ay(4) - Date
  16.          ElseIf InStr(.Cells(C.Row, A.Column), "~") > 0 Then
  17.          Ay(3) = Evaluate(Replace(.Cells(C.Row, A.Column), "~", "+")) / 2
  18.          Ay(4) = .Cells(C.Row, A.Column + 1): Ay(5) = Ay(3) - Ay(4)
  19.          Else
  20.          GoTo 10
  21.       End If
  22.     ReDim Preserve Ary(s)
  23.     Ary(s) = Ay
  24.     Erase Ay: s = s + 1
  25. 10
  26.     Next
  27. Next
  28. End With
  29. Sheet2.UsedRange.Offset(2) = ""
  30. Sheet2.[A3].Resize(s, 6) = Application.Transpose(Application.Transpose(Ary))
  31. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 5# Hsieh


    ·PÁª©¤jªº«ü¾É,´ú¸Õ«áµo²{³¡¥÷¸ê®Æ·|³y¦¨µ{¦¡§P§O²§±`,°µ³¡¥÷ªº­×§ï«á,°ÝÃD¤w±o¸Ñ¨M,ÁÂÁÂ

TOP

        ÀR«ä¦Û¦b : ¹D¼w¬O´£ª@¦Û§Úªº©ú¿O¡A¤£¸Ó¬O¨þ¥¸§O¤HªºÃ@¤l¡C
ªð¦^¦Cªí ¤W¤@¥DÃD