|
发表于 2016-4-15 15:32
|
显示全部楼层
本楼为最佳答案
注意:源数据的表头中不要有换行符,不然查询内容无法匹配- Sub 查询()
- Set d = CreateObject("scripting.dictionary")
- cx = "品名,规格,批号,投料量,单价,半成品数量,半成品收率,成品数量,成品收率,成品定额,定额数量,节超数量,节约金额,杂质,灰屑,质量情况,备注"
- cxrr = Split(cx, ",")
- arr = Sheets("数据统计2016").[a1].CurrentRegion
- ReDim brr(1 To UBound(arr), 1 To UBound(cxrr) + 1)
- For j = 1 To UBound(arr, 2) '表头与列号挂钩
- d(arr(1, j)) = j
- Next
- For j = 0 To UBound(cxrr) '查询内容与源表列号挂钩
- cxrr(j) = d(cxrr(j))
- Next
-
- rq1 = [b3]: rq2 = [c3]
- For i = 1 To UBound(arr)
- rq = arr(i, 15)
- If rq >= rq1 And rq <= rq2 Then
- n = n + 1
- For j = 1 To UBound(brr, 2)
- brr(n, j) = arr(i, cxrr(j - 1))
- Next
- End If
- Next
- [a8:z1000] = ""
- [a8].Resize(1, UBound(brr, 2)) = Split(cx, ",")
- [a9].Resize(n, UBound(brr, 2)) = brr
- End Sub
复制代码 |
|