|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub My转换()
Dim ar, br(), i&, r&, k&, s$, t, tt!
tt = Timer
r = Sheet2.Range("e" & Rows.Count).End(xlUp).Row - 6
ar = Sheet2.Range("C7").Resize(r, 66)
r = 0
ReDim br(1 To 1000, 1 To 5)
t = [{"节次","课程","教室","教师","学生人数"}]
Sheet4.Range("a1").Resize(, UBound(t)) = t
For i = 3 To UBound(ar) Step 3
For k = 3 To UBound(ar, 2)
s = ar(i, k)
If s <> "" Then
r = r + 1
br(r, 1) = ar(i, 1)
br(r, 2) = s
br(r, 3) = ar(1, k)
br(r, 4) = ar(i + 2, k)
br(r, 5) = ar(i + 1, k)
End If
Next
Next
If r > 0 Then
Application.ScreenUpdating = False
Sheet4.Range("a2").Resize(r, UBound(t)) = ""
Sheet4.Range("a2").Resize(r, UBound(t)) = br
' With Sheet4.Range("A:E")
' .HorizontalAlignment = xlCenter
' .VerticalAlignment = xlCenter
' .EntireColumn.AutoFit
' .Borders.LineStyle = xlContinuous
' End With
MsgBox "亲~数据转换成功" & Chr(10) & "程序共运行" & _
Format(Timer - tt, "0.00") & "秒", 64, "新年快乐"
Application.ScreenUpdating = True
End If
End Sub |
|