|
发表于 2014-8-23 15:36
|
显示全部楼层
本楼为最佳答案
乡镇固定列的代码。- Private Sub CommandButton1_Click()
- Dim d As Object, arr, ori, i%, brr(1 To 1000, 4)
- Set d = CreateObject("scripting.dictionary") '镇
- Set d1 = CreateObject("scripting.dictionary") '身份证
- Set d2 = CreateObject("scripting.dictionary") '大于10亩身份证
- With Sheets("数据库")
- ori = .Range("A4:u" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
- End With
- For i = 1 To UBound(ori)
- If ori(i, 1) <> "" Then
- sr = ori(i, 15) '15数据库-镇
- sr1 = ori(i, 21) '21数据库-身份证
- ms = ori(i, 5) '亩数
- If Not d.exists(sr) Then
- n = n + 1
- d(sr) = n
- ' brr(n, 0) = sr
- End If
- k = d(sr)
- If Not d1.exists(sr1) Then brr(k, 1) = brr(k, 1) + 1: d1(sr1) = "" '户数总计
- brr(k, 2) = brr(k, 2) + ms '亩数总计
- If ms > 10 Then
- brr(k, 4) = brr(k, 4) + ms '大于10亩的亩数总计
- If Not d2.exists(sr1) Then brr(k, 3) = brr(k, 3) + 1: d2(sr1) = "" '大于10亩的户数总计
- End If
- End If
- Next
- With Sheets("汇总")
- crr = .Range("a6:e" & .[a65536].End(3).Row)
- For i = 1 To UBound(crr)
- sr = crr(i, 1)
- k = d(sr)
- For j = 2 To 5
- crr(i, j) = brr(k, j - 1)
- Next
- Next
- .Range("a6:e" & .[a65536].End(3).Row) = crr
- End With
- End Sub
复制代码 |
|