|
分组、场次代码
- Private Function 分组模块(ByVal xCount As Integer) As Range
- Select Case xCount
- Case Is = 3: Set 分组模块 = Sheet1.Range("A1:BN7")
- Case Is = 4: Set 分组模块 = Sheet1.Range("A9:BN17")
- Case Is = 5: Set 分组模块 = Sheet1.Range("A19:BN29")
- Case Is = 6: Set 分组模块 = Sheet1.Range("A31:BN43")
- End Select
- End Function
- Private Function 对阵轮次规则(ByVal xCount As Integer)
- Select Case xCount
- Case Is = 3: 对阵轮次规则 = "23|13|12"
- Case Is = 4: 对阵轮次规则 = "14,23|13,42|12,34"
- Case Is = 5: 对阵轮次规则 = "25,34|15,23|14,53|13,42|12,45"
- Case Is = 6: 对阵轮次规则 = "16,25,34|15,64,23|14,53,62|13,42,56|12,36,45"
- End Select
- End Function
- Sub 生成比赛场次()
- Dim Sh As Worksheet, Sht As Worksheet, R%
- Set Sh = Sheets("运动员")
- Set Sht = Sheets("竞赛分组")
- R = Sh.Range("A65536").End(3).Row
- If R < 4 Then MsgBox "运动员表没有报名数据!": Exit Sub
- If MsgBox("执行本操作将导致现有数据丢失,请谨慎操作!" & vbCrLf & _
- "选择“是”生成比赛场次,以及竞赛分组;" & vbCrLf & _
- "选择“否”退出当前操作。", vbQuestion + vbYesNo, "重要提示") = vbNo Then
- Exit Sub
- End If
- Application.DisplayAlerts = False
- Application.ScreenUpdating = False
-
- Rem 数据整理(排序)
- Sh.Sort.SortFields.Clear
- Sh.Sort.SortFields.Add Range("D4:D" & R), 0, 1, 0, 0
- Sh.Sort.SortFields.Add Range("E4:E" & R), 0, 1, 0, 0
- With Sh.Sort
- .SetRange Range("A3:F" & R)
- .Header = xlYes
- .MatchCase = False
- .Orientation = xlTopToBottom
- .SortMethod = xlPinYin
- .Apply
- End With
-
- Rem 获取分组信息
- Dim arr, i%, j%, d As Object
- arr = Sh.Range("A4:F" & R)
- Set d = CreateObject("scripting.dictionary")
- For i = 1 To UBound(arr)
- If Not d.Exists(arr(i, 4)) Then
- d(arr(i, 4)) = arr(i, 1)
- Else
- d.Item(arr(i, 4)) = d.Item(arr(i, 4)) & "|" & arr(i, 1)
- End If
- Next
-
- Rem 编排比赛场次
- Dim 项目$, k, ydy, t1, t2, t3, m%, n%
- Dim a(), p% '【比赛场次】数据
- Dim Rng As Range, setRng As Range
- 项目 = Sh.Range("B2").Text
- Sht.Unprotect Password:="123"
- With Sht.Range("A1:BQ2000")
- .UnMerge
- .Borders.LineStyle = xlNone
- .Borders(xlDiagonalDown).LineStyle = xlNone
- .ClearContents
- End With
- Set setRng = Sht.Range("A1")
-
- For Each k In d.keys '遍历分组
- j = 0
- m = 0: n = 0
- ydy = Split(d.Item(k), "|")
-
- t1 = 对阵轮次规则(UBound(ydy) + 1)
- Set Rng = 分组模块(UBound(ydy) + 1)
- Rem 复制粘贴相应模块
- Rng.Copy
- With setRng
- .PasteSpecial
- .Value = 项目 & k
- For j = 0 To UBound(ydy)
- .Offset(j * 2 + 1, 0).Offset(0, 1) = ydy(j)
- Next
- End With
-
- Rem 生成当前组的比赛轮次
- For Each t2 In Split(t1, "|") '遍历轮次
- m = m + 1
- For Each t3 In Split(t2, ",") '遍历场次
- n = n + 1: p = p + 1
- Rem 【比赛场次】数据
- ReDim Preserve a(1 To 6, 1 To p)
- a(1, p) = 项目 & k '项目与分组
- a(2, p) = "第" & m & "轮" '轮次
- a(3, p) = n '场序
- a(4, p) = ydy(Mid(t3, 1, 1) - 1) '甲方
- a(5, p) = ydy(Mid(t3, 2, 1) - 1) '乙方
- Next
- Next
- Set setRng = setRng.Offset(Rng.Rows.Count + 1, 0) '下次粘贴位置
- Next
-
- Rem 输出到【比赛场次】
- With Sheets("比赛场次")
- .Unprotect Password:="123"
- .Range("A2:E1000").ClearContents
- .Range("A2").Resize(p, 6) = WorksheetFunction.Transpose(a)
- .Protect Password:="123"
- End With
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- Sht.Protect Password:="123"
- Set d = Nothing
- Set Sh = Nothing
- Set Sht = Nothing
- MsgBox "OK"
- End Sub
复制代码
|
评分
-
1
查看全部评分
-
|