|
轉換有值的資料,你自行修改一下吧;
- Sub zz()
- Dim a, b(), n&, aa, k, t, r As Object, m&
- Set r = CreateObject("vbscript.regexp")
- r.Global = True
- r.Pattern = "\s+"
- aa = ActiveSheet.UsedRange
- a = Array(Array(1, 9, 19), Array(3, 11, 21))
- n = (UBound(aa) - 4) / 2
- ReDim b(1 To n * UBound(aa, 2) + 3, 1 To 10)
- n = 4
- For i = 5 To UBound(aa) Step 2
- For jj = 1 To UBound(aa, 2)
- For j = 0 To 2
- b(n, j + 1) = aa(i, a(1)(j))
- Next
- k = r.Replace(aa(i + 1, jj), "")
- m = 4
- b(n, m) = aa(4, jj)
- For j = 1 To Len(k) Step 5
- m = m + 1
- b(n, m) = Mid(k, j, 5)
- Next
- n = n + 1
- Next
- Next
- b(1, 1) = aa(1, 1)
- For j = 0 To 2
- b(3, j + 1) = aa(5, a(0)(j))
- Next
- For j = 1 To UBound(aa, 2)
- If Len(aa(3, j)) > 0 Then b(2, 1) = b(2, 1) & " " & aa(3, j)
- Next
- b(2, 1) = Mid(b(2, 1), 2)
- Workbooks.Add 1
- [a1].Resize(UBound(b), UBound(b, 2)) = b
- [d3:j3] = Array("Date", "AM in", "AM out", "PM in", "PMout ", "OT in", "OT out")
- [e4].Resize(UBound(b) - 3, 6).NumberFormatLocal = "hh:mm"
- Set r = Nothing
- End Sub
复制代码 |
|