|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Dim reg As New RegExp
- Dim flg As Boolean
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- With reg
- .Pattern = "^(\d+)\.题"
- End With
- With Worksheets("源作业")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- c = .Cells(1, .Columns.Count).End(xlToLeft).Column
- arr = .Range("a1").Resize(r, c)
- End With
- For j = 1 To UBound(arr, 2)
- Set mh = reg.Execute(arr(1, j))
- If mh.Count > 0 Then
- th = Val(mh(0).SubMatches(0))
- d1(j) = th
- End If
- Next
- With Worksheets("源作业")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- c = .Cells(1, .Columns.Count).End(xlToLeft).Column
- arr = .Range("a1").Resize(r, c)
- For i = 2 To UBound(arr)
- xm = arr(i, UBound(arr, 2))
- If Not d.exists(xm) Then
- ReDim brr(1 To th + 2)
- brr(1) = xm
- Else
- brr = d(xm)
- End If
- For j = 8 To UBound(arr, 2) - 2
- If d1.exists(j) Then
- n = d1(j) + 1
- If Len(arr(i, j)) <> 0 Then
- brr(n) = brr(n) & Split(arr(i, j), ".")(1)
- End If
- End If
- Next
- d(xm) = brr
- Next
- End With
- With Worksheets("评分")
- c = .Cells(1, .Columns.Count).End(xlToLeft).Column
- crr = .Range("a1").Resize(1, c)
- End With
- brr = Application.Transpose(Application.Transpose(d.items))
- For j = 2 To UBound(brr, 2) - 1
- For i = 1 To UBound(brr)
- If Len(brr(i, j)) <> 0 Then
- If Len(crr(1, j)) = 1 Then
- If brr(i, j) = crr(1, j) Then
- brr(i, j) = "5|" & brr(i, j)
- Else
- brr(i, j) = "0|" & brr(i, j)
- End If
- Else
- If Len(brr(i, j)) = Len(crr(1, j)) Then
- If brr(i, j) = crr(1, j) Then
- brr(i, j) = "5|" & brr(i, j)
- Else
- brr(i, j) = "0|" & brr(i, j)
- End If
- ElseIf Len(brr(i, j)) > Len(crr(1, j)) Then
- brr(i, j) = "0|" & brr(i, j)
- Else
- flg = True
- For k = 1 To Len(brr(i, j))
- ch = Mid(brr(i, j), k, 1)
- If InStr(crr(1, j), ch) = 0 Then
- flg = False
- Exit For
- End If
- Next
- If flg Then
- brr(i, j) = "2|" & brr(i, j)
- Else
- brr(i, j) = "0|" & brr(i, j)
- End If
- End If
- End If
- End If
- Next
- Next
- For i = 1 To UBound(brr)
- For j = 2 To UBound(brr, 2) - 1
- brr(i, UBound(brr, 2)) = brr(i, UBound(brr, 2)) + Val(brr(i, j))
- Next
- Next
- With Worksheets("评分")
- .Range("a3").Resize(UBound(brr), UBound(brr, 2)) = brr
- End With
- End Sub
复制代码 |
|