|
楼主 |
发表于 2014-11-12 09:33
|
显示全部楼层
下面这条代码是直接提取全部数据的分不了城镇职工或城镇居民。如何修改真能实现根据B2、F2、J2的的条件来提取。- Sub tq()
- Dim ar, ar1, ar2(), ar3(14, 6), i&, m%, k%, s$$$$
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- ar = Sheets("数据库").Cells(1, 1).CurrentRegion
- ar1 = Range("b4:j18")
- ReDim ar2(1 To UBound(ar), 1 To 9)
- For i = 2 To UBound(ar)
- s = ar(i, 2) & "|" & ar(i, 5)
- If d(s) = "" Then
- k = k + 1: d(s) = k
- ar2(k, 1) = ar(i, 3): ar2(k, 2) = ar(i, 5)
- For m = 6 To 12
- ar2(k, m - 3) = ar(i, m)
- Next m
- Else
- For m = 6 To 12
- ar2(k, m - 3) = ar2(k, m - 3) + ar(i, m)
- Next m
- End If
- Next i
- For i = 2 To UBound(ar1)
- If ar1(i, 1) = "" Then ar1(i, 1) = ar1(i - 1, 1)
- Next i
- For i = 1 To UBound(ar1)
- s = ar1(i, 1) & "|" & ar1(i, 2)
- If d(s) <> "" Then
- For m = 0 To 6
- ar3(i - 1, m) = ar2(d(s), m + 3)
- Next m
- End If
- Next i
- [d4].Resize(15, 7) = ""
- [d4].Resize(15, 7) = ar3
- End Sub
复制代码 |
|