- Sub test0()
- Dim data, para, dict As Object
- Dim i As Long, j As Long, col As Long, pos As Long
-
- Set dict = CreateObject("Scripting.Dictionary")
- para = Worksheets("参数设置").Range("A1").CurrentRegion.Value
- para(6, 1) = "待合格"
- For j = 2 To UBound(para, 2)
- dict.Add para(1, j), j
- Next
-
- With Worksheets("原始成绩").Range("A1").CurrentRegion
- data = Range(.Cells(1), .Offset(, 1)).Value
- End With
- data(1, 1) = "质量监测成绩等级"
- data(2, 3) = data(2, 4)
- data(2, UBound(data, 2)) = "总分"
-
- For j = 5 To UBound(data, 2)
- data(2, j - 1) = data(2, j)
- col = dict(data(2, j - 1))
- For i = 3 To UBound(data)
- Select Case Val(data(i, j))
- Case Is >= para(3, col)
- pos = 3
- Case Is >= para(4, col)
- pos = 4
- Case Is >= para(5, col)
- pos = 5
- Case Else
- pos = 6
- End Select
- If j = 5 Then data(i, 3) = data(i, 4)
- data(i, j - 1) = para(pos, 1)
- If j < UBound(data, 2) Then data(i, UBound(data, 2)) = data(i, UBound(data, 2)) + Val(data(i, j))
- Next
- Next
-
- With Worksheets("成绩等级(结果)").Range("A1")
- .CurrentRegion.ClearContents
- .Resize(UBound(data), UBound(data, 2) - 1) = data
- End With
-
- Set dict = Nothing
- Beep
- End Sub
复制代码 |