|
本帖最后由 dyzx 于 2016-11-24 16:06 编辑
各位老师:请问如何将表1格式转换成表2格式,请多多指教。
把 张生76 初二1—4音乐 初一10、11 初三1—5音乐 舞蹈队
改为 张生76 初二1—4音乐 初一10、11音乐 - Sub 转换()
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets(1).[a1].CurrentRegion
- For i = 3 To UBound(arr)
- For j = 2 To UBound(arr, 2) Step 3
- xm = arr(i, j) '姓名
- If Len(xm) > 0 Then
- rk = arr(i, j + 1) '任课
- If InStr(rk, "初") > 0 Then
- rkrr = Split(rk, " ")
- For Each rk In rkrr
- If InStr(rk, "初") > 0 And InStr(rk, "任") = 0 Then '去掉“班任”等
- nj = Left(rk, 2) '年级
- bj = Mid(rk, 3) '班级+任课
- For k = Len(bj) To 1 Step -1
- If IsNumeric(Mid(bj, k, 1)) Then Exit For
- Next
- km = Mid(bj, k + 1) '任课
- bj = Replace(bj, km, "") '班级
- If Len(bj) = 0 Then bj = "1—20"
- p = InStr(bj, "—")
- If p > 0 Then '班级中含“—”,1—5 转换成1、2、3、4、5
- s = Val(bj): e = Val(Mid(bj, p + 1))
- bj = ""
- For k = s To e
- bj = bj & "、" & k
- Next
- bj = Mid(bj, 2)
- End If
- bjrr = Split(bj, "、")
- For k = 0 To UBound(bjrr)
- x = nj & "(" & bjrr(k) & ")班" & km '年级+班级+任课为key
- d(x) = xm
- Next
- End If
- Next
- End If
- End If
- Next
- Next
- With Sheets(2)
- .[b3:o100] = ""
- arr = .[a1].CurrentRegion
- For i = 3 To UBound(arr)
- For j = 2 To UBound(arr, 2)
- x = arr(i, 1) & arr(2, j)
- arr(i, j) = d(x)
- Next
- Next
- .[a1].CurrentRegion = arr
- End With
- End Sub
复制代码初三1—5音乐 舞蹈队
|
|