|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
'1楼附件,按输出格式给你凑了一个,自己修改,,,
Option Explicit
Sub test()
Dim arr, i, j, k, p, t, m, s, dic
With Sheets("排课表")
arr = .Range("a1:e" & .Cells(Rows.Count, "a").End(xlUp).Row + 1)
ReDim brr(1 To UBound(arr, 1) * 4, 1 To 4)
For i = 2 To UBound(arr, 1) - 1
t = Replace(Replace(arr(i, 3), ":", ":"), Space(1), vbNullString)
t = Split(arr(i, 3), "-")
arr(i, 5) = Format(Split(t(0), ":")(0), "00") & Format(Split(t(0), ":")(1), "00") & _
Format(Split(t(1), ":")(0), "00") & Format(Split(t(1), ":")(1), "00")
Next
p = 2
Call bsort(arr, 2, UBound(arr, 1) - 1, 1, UBound(arr, 2), 1)
For i = 2 To UBound(arr, 1) - 1
If arr(i, 1) <> arr(i + 1, 1) Then
Call bsort(arr, p, i, 1, UBound(arr, 2), 2)
For j = p To i
If arr(j, 1) <> arr(j + 1, 1) Or arr(j, 2) <> arr(j + 1, 2) Then
Call bsort(arr, p, j, 1, UBound(arr, 2), 5)
For k = p To j
s = s & vbNewLine & arr(k, 4)
If arr(k, 1) <> arr(k + 1, 1) Or arr(k, 2) <> arr(k + 1, 2) Or arr(k, 5) <> arr(k + 1, 5) Then
m = m + 1
brr(m, 1) = arr(k, 1): brr(m, 2) = arr(k, 2): brr(m, 3) = arr(k, 3): brr(m, 4) = Mid(s, 2)
p = k + 1: s = vbNullString
End If
Next
p = j + 1
End If
Next
p = i + 1
End If
Next
Call doevent(False)
Set p = Sheets("排课表").[a1:d1]
Set dic = CreateObject("scripting.dictionary")
For Each i In Sheets
If i.Name <> "排课表" Then
i.Delete
End If
Next
For i = 1 To m
If dic.exists(brr(i, 1)) Then
dic(brr(i, 1)) = dic(brr(i, 1)) + 1
s = brr(i, 1) & dic(brr(i, 1))
Else
s = brr(i, 1)
dic(s) = 0
End If
Sheets.Add
With ActiveSheet
.Name = s
p.Copy .[a1]
For j = 1 To UBound(brr, 2)
.Cells(2, j) = brr(i, j)
Next
.[a1].Resize(2, UBound(brr, 2)).Borders.LineStyle = xlContinuous
End With
Next
End With
Call doevent(True)
End Sub
Function doevent(flag As Boolean)
With Application
.DisplayAlerts = flag
.ScreenUpdating = flag
End With
End Function
Function bsort(arr, first, last, left, right, key)
Dim i, j, k, t
For i = first To last - 1
For j = first To last + first - 1 - i
If arr(j, key) > arr(j + 1, key) Then
For k = left To right
t = arr(j, k): arr(j, k) = arr(j + 1, k): arr(j + 1, k) = t
Next
End If
Next
Next
End Function |
评分
-
1
查看全部评分
-
|