|
发表于 2012-8-7 14:24
|
显示全部楼层
本楼为最佳答案
本帖最后由 suye1010 于 2012-8-7 14:45 编辑
这句代码有问题,如果这个村只有一个人参保的话,在你去除最后一个","后,这个村的数据就不满足条件了,所以这个村就不统计了
If InStr(t(i), ",") Then
aa = Split(t(i), ",")- Sub lqxs()
- Dim Arr, i&, aa, j&, Brr, z, nl
- Dim d, k, t
- Set d = CreateObject("Scripting.Dictionary")
- Sheet1.Activate
- Arr = Sheet2.[a1].CurrentRegion
- For i = 3 To UBound(Arr)
- d(Arr(i, 16)) = d(Arr(i, 16)) & i & ","
- Next
- k = d.keys
- t = d.items
- ReDim Brr(1 To d.Count, 1 To 24) '你特殊参保在第24列,定义数组的时候列数少于24当然不能显示出来
- For i = 0 To UBound(k)
- Brr(i + 1, 1) = i + 1
- Brr(i + 1, 2) = k(i)
- ' t(i) = Left(t(i), Len(t(i)) - 1) 这些可以不用先处理
- ' If InStr(t(i), ",") Then
- aa = Split(t(i), ",")
- For j = 0 To UBound(aa) - 1 '在这里不要去最后一个元素即相当于去除最后一个逗号
- z = Arr(aa(j), 4)
- If Val(Mid(z, 17, 1)) Mod 2 = 1 Then
- Brr(i + 1, 4) = Brr(i + 1, 4) + 1
- Else
- Brr(i + 1, 5) = Brr(i + 1, 5) + 1
- End If
- Brr(i + 1, 3) = Brr(i + 1, 3) + 1
- nl = DateSerial(Mid(z, 7, 4), Mid(z, 11, 2), Mid(z, 13, 2))
- nl = DateDiff("yyyy", nl, Date)
- Select Case nl
- Case 16 To 35
- Brr(i + 1, 7) = Brr(i + 1, 7) + 1
- Case 36 To 44
- Brr(i + 1, 8) = Brr(i + 1, 8) + 1
- Case 45 To 59
- Brr(i + 1, 9) = Brr(i + 1, 9) + 1
- Case 60 To 69
- Brr(i + 1, 10) = Brr(i + 1, 10) + 1
- Case 70 To 79
- Brr(i + 1, 11) = Brr(i + 1, 11) + 1
- Case 80 To 89
- Brr(i + 1, 12) = Brr(i + 1, 12) + 1
- Case Is >= 90
- Brr(i + 1, 13) = Brr(i + 1, 13) + 1
- End Select
- Brr(i + 1, 6) = Brr(i + 1, 6) + 1
- Select Case Arr(aa(j), 9)
- Case 100
- Brr(i + 1, 14) = Brr(i + 1, 14) + 1
- Case 200
- Brr(i + 1, 15) = Brr(i + 1, 15) + 1
- Case 300
- Brr(i + 1, 16) = Brr(i + 1, 16) + 1
- Case 400
- Brr(i + 1, 17) = Brr(i + 1, 17) + 1
- Case 500
- Brr(i + 1, 18) = Brr(i + 1, 18) + 1
- End Select
- If Arr(aa(j), 12) <> "" Then Brr(i + 1, 24) = Brr(i + 1, 24) + 1 '这里是处理特殊参保人群
- Next
- ' End If
- Next
- [A7].Resize(100, 100).ClearContents
- [A7].Resize(UBound(Brr, 1), UBound(Brr, 2)) = Brr
- End Sub
复制代码 |
|