|
发表于 2014-1-8 17:48
|
显示全部楼层
本楼为最佳答案
- <P>Sub test()
- Dim rg As Range, rng As Range
- Dim k%, m%, i%
- Dim arr()
- '--------------------------------------------------------------------------------------
- Set rng = Sheets("数据").Range("a1:a" & Range("a65536").End(3).Row)
- '--------------------------------------------------------------------------------------
- For Each rg In rng
- If rg = "组别:" Then
- brr = rg.CurrentRegion
- ReDim Preserve arr(1 To 100, 1 To 7)
- For i = 3 To UBound(brr)
- k = k + 1
- arr(k + 1, 1) = brr(i, 1): arr(k + 1, 2) = brr(i, 5): arr(k + 1, 3) = brr(i, 2)
- arr(k + 1, 4) = brr(i, 3): arr(k + 1, 5) = brr(i, 4): arr(k + 1, 6) = rg.Offset(, 1)
- If Len(brr(i, 2)) = 0 Then
- n = 1 + Len(brr(i, 3)) - Len(Replace(brr(i, 3), " ", ""))
- Else
- n = 1 + Len(brr(i, 2) & " " & brr(i, 3)) - Len(Replace(brr(i, 2) & " " & brr(i, 3), " ", ""))
- End If
- arr(k + 1, 7) = n
- Next i
- End If
- Set brr = Nothing
- Next rg
- '--------------------------------------------------------------------------------------
- arr(1, 1) = "名次": arr(1, 2) = "背号": arr(1, 3) = "男选手": arr(1, 4) = "女选手"
- arr(1, 5) = "参赛单位": arr(1, 6) = "组别": arr(1, 7) = "男女选手总人数":
- '--------------------------------------------------------------------------------------
- Sheets.Add
- Range("a2").Resize(UBound(arr), 7) = arr
- Cells.EntireColumn.AutoFit
- ActiveSheet.Name = "汇总表"</P>
- <P>End Sub
- </P>
复制代码 |
|