- 帖子
- 4901
- 主題
- 44
- 精華
- 24
- 積分
- 4916
- 點名
- 91
- 作業系統
- Windows 7
- 軟體版本
- Office 20xx
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台北
- 註冊時間
- 2010-4-30
- 最後登錄
- 2024-4-20
              
|
回復 4# jcchiang - Sub ex()
- Dim Ay(), Ary(), A As Range, C As Range
- With Sheet1
- For Each A In .Range(.[B4], .[IV4].End(xlToLeft)).SpecialCells(xlCellTypeConstants)
- For Each C In .Range(.[A5], .[A5].End(xlDown))
- If InStr(C, "-") > 0 Then
- ar = Split(C, "-")
- Else
- ar = Split(C & "-(", "-")
- End If
- ReDim Ay(UBound(ar) + 4)
- Ay(0) = A: Ay(1) = ar(0): Ay(2) = Left(ar(1), InStr(ar(1), "(") - 1)
- 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
- ElseIf InStr(.Cells(C.Row, A.Column), "~") > 0 Then
- Ay(3) = Evaluate(Replace(.Cells(C.Row, A.Column), "~", "+")) / 2
- Ay(4) = .Cells(C.Row, A.Column + 1): Ay(5) = Ay(3) - Ay(4)
- Else
- GoTo 10
- End If
- ReDim Preserve Ary(s)
- Ary(s) = Ay
- Erase Ay: s = s + 1
- 10
- Next
- Next
- End With
- Sheet2.UsedRange.Offset(2) = ""
- Sheet2.[A3].Resize(s, 6) = Application.Transpose(Application.Transpose(Ary))
- End Sub
|
|
發表於 2013-2-7 16:30
| 