|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- Sub 分组()
- Dim vData As Variant, nRow As Integer, oDic As Object, sName As String
- Dim nTeams As Integer, nTeam As Integer, nPerson As Integer, nAVG As Integer
-
- With Sheet1
- nTeams = Val(.[D1].Value)
- nPerson = Val(.[H1].Value)
- If nTeams = 0 Or nPerson = 0 Then
- MsgBox "分组参数未设置!"
- Exit Sub
- End If
- vData = .[A1].CurrentRegion.Resize(, 1).Value
- Set oDic = CreateObject("Scripting.Dictionary")
- For nRow = 2 To UBound(vData)
- If Trim(vData(nRow, 1)) <> "" Then oDic(Trim(vData(nRow, 1))) = 0
- Next
- nAVG = Int((oDic.Count - 1) / nTeams) + 1
- If nAVG > nPerson Then nAVG = nPerson
- ReDim vData(1 To nTeams, 1 To nAVG)
- For nTeam = 1 To nTeams
- For nPerson = 1 To nAVG
- If oDic.Count > 0 Then
- Randomize
- nRow = (oDic.Count - 1) * Rnd()
- sName = oDic.Keys()(nRow)
- oDic.Remove sName
- vData(nTeam, nPerson) = sName
- End If
- Next
- Next
- End With
- With Sheet2
- .UsedRange.ClearContents
- .[A2].Resize(nTeams, nAVG) = vData
- End With
- End Sub
复制代码 |
|