|
发表于 2014-4-25 14:49
|
显示全部楼层
本楼为最佳答案
- Dim arr, brr, d
- Private Sub CommandButton1_Click() '查询
- Set d1 = CreateObject("scripting.dictionary")
- dbd = Me.ComboBox1 '代表队
- With Sheet1
- .[a2] = dbd
- .Range("b3:b5,d3:d5,f3:f5,a7:f1000") = ""
- For i = 2 To UBound(brr)
- If dbd = Trim(brr(i, 2)) Then
- ld = Trim(brr(i, 3))
- jl = Trim(brr(i, 5))
- .[b3] = ld: .[b4] = jl '领队、教练
- .[d3] = brr(i, 4): .[d4] = brr(i, 6) '电话
- If Len(ld) > 0 Then .[f3] = UBound(Split(ld, " ")) + 1
- If Len(jl) > 0 Then .[f4] = UBound(Split(jl, " ")) + 1 '人数
- .[b5] = brr(i, 7)
- End If
- Next
-
- ReDim crr(1 To 1000, 1 To 6)
- For i = 2 To UBound(arr)
- If dbd = Trim(arr(i, 4)) Then
- male = Trim(arr(i, 2)): female = Trim(arr(i, 3))
- x = male & " " & female '男+女,计算总人数用
- n = n + 1
- crr(n, 1) = n '序号
- crr(n, 2) = male: crr(n, 3) = female
- crr(n, 4) = arr(i, 5)
- xm = Trim(arr(i, 6)) & "," & Trim(arr(i, 7)) '项目
- If Right(xm, 1) = "," Then xm = Left(xm, Len(xm) - 1)
- crr(n, 5) = xm
- xrr = Split(x, " ") '计算总人数
- For j = 0 To UBound(xrr)
- xkey = Trim(xrr(j))
- If Len(xkey) > 0 Then d1(xkey) = ""
- Next
- End If
- Next
- .[f5] = d1.Count '选手人数
- .[a7].Resize(n, 6) = crr
- End With
- End Sub
- Private Sub CommandButton2_Click() '退出
- Unload Me
- End Sub
- Private Sub UserForm_Initialize() '初始化
- Set wb = Workbooks.Open(ThisWorkbook.Path & "\比赛文件.xls")
- Set d = CreateObject("scripting.dictionary")
- arr = wb.Sheets(1).[a1].CurrentRegion
- brr = wb.Sheets(2).[a1].CurrentRegion
- wb.Close False
- For i = 2 To UBound(arr)
- xkey = Trim(arr(i, 4))
- d(xkey) = d(xkey) + 1
- Next
- Me.ComboBox1.List = Application.Transpose(d.keys)
- End Sub
复制代码 |
|