|
本帖最后由 dyzx 于 2015-2-1 09:44 编辑
请老师帮帮助,如果将表1转换成表2应怎样修改里面的代码,多谢指教,多谢
dyzx 发表于 2015-1-30 08:35
hwc2ycy老师:非常多谢你的耐心指教,就是除了“**长、主任、书记”等外,其它就是“教师”职务,多谢[/b ... - Sub 转换()
- Dim r%, i%, m%, j%
- Dim arr, brr(1 To 1000, 1 To 6)
- Dim arrT1, arrT2
- Dim str1$, str2$, str3$ ', str4$
- Dim reg As Object, mh As Object
- 'Dim reg As RegExp
- Dim strKm$, strNj$, strBj$, strZw$
- str1 = "(初一|初二|初三)(.*?)(语文|数学|英语|政治|历史|音乐|化学|物理|地理|信息技术|生物|体育|美术|综合实践|写字课)"
- '职务
- str2 = "[^\s}]+?(长|任|记)"
- '去多余的空格
- str3 = "\s+"
- Application.ScreenUpdating = False
- Set reg = CreateObject("VBSCRIPT.REGEXP") '引用正则表达式
- reg.Global = True
- m = 0
- With Worksheets("表1")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a3:l" & r)
- End With
- 'brr: 序号 姓名 职务 任教科目 任教年级 任教班级
- For j = 1 To UBound(arr, 2) Step 3
- For i = 1 To UBound(arr)
- If Len(arr(i, j + 1)) <> 0 Then
- strKm = "": strNj = "": strBj = "": strZw = ""
- reg.Pattern = str3
- arrT2 = Split(reg.Replace(arr(i, j + 2), " "))
- m = m + 1
- brr(m, 1) = m '序号
- brr(m, 2) = arr(i, j + 1) '姓名
- For Each arrT1 In arrT2
- With reg
- '职务
- .Pattern = str2
- If .Test(arrT1) Then
- Set mh = .Execute(arrT1)
- strZw = strZw & mh(0) & "/"
- Else
- '班级,科目
- .Pattern = str1
- If .Test(arrT1) Then
- Set mh = .Execute(arrT1)
- If InStr(1, strKm, mh(0).SubMatches(2), vbBinaryCompare) = 0 Then strKm = strKm & mh(0).SubMatches(2) & ","
- 'brr(m, 4) = brr(m, 4) & mh(0).SubMatches(2) & ","
- If InStr(1, strNj, mh(0).SubMatches(0), vbBinaryCompare) = 0 Then strNj = strNj & mh(0).SubMatches(0) & ","
- 'brr(m, 5) = brr(m, 5) & mh(0).SubMatches(0) & ","
- If mh(0).SubMatches(1) <> "级" Then
- If InStr(1, strBj, mh(0).SubMatches(1), vbBinaryCompare) = 0 Then strBj = strBj & mh(0).SubMatches(1) & ","
- 'brr(m, 6) = brr(m, 6) & mh(0).SubMatches(1) & ","
- Else
- If InStr(1, strBj, "全级", vbBinaryCompare) = 0 Then strBj = strBj & "全级,"
- End If
- End If
- End If
- End With
- Next
- If Len(strZw) Then
- brr(m, 3) = Left(strZw, Len(strZw) - 1)
- Else
- brr(m, 3) = "教师"
- End If
-
- If Len(strKm) Then brr(m, 4) = Left(strKm, Len(strKm) - 1)
- If Len(strNj) Then brr(m, 5) = Left(strNj, Len(strNj) - 1)
- If Len(strBj) Then brr(m, 6) = Left(strBj, Len(strBj) - 1)
- End If
- Next
- Next
- With Worksheets("表2")
- With .Range("a3").Resize(UBound(brr), UBound(brr, 2))
- .Value = brr
- .Borders.LineStyle = xlContinuous
- .Font.Size = 10
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- End With
- End With
- Application.ScreenUpdating = True
- MsgBox "转换完成", vbInformation + vbOKOnly, "提示"
- End Sub
复制代码
|
|