本帖最后由 芐雨 于 2015-1-25 10:13 编辑
用数组更简单- Sub t()
- Dim arr, brr, i&, j&, n%
- Dim y%, h%, k%
- y = [B1]: h = [D1] '等到年,月
- ReDim brr(1 To 31, 1 To 4) ' 创建数组
- [A3:D33].ClearContents
- For k = 1 To 3
- 'CStr(k) 把数字转化成文本,Sheets(CStr(k))得到工作表
- arr = Sheets(CStr(k)).Range("A1:D" & Sheets(CStr(k)).Cells(Rows.Count, 1).End(3).Row)
- For i = 2 To UBound(arr) '遍历数组
- If arr(i, 1) = y And arr(i, 2) = h Then '年,月相等时
- If arr(i, 3) > n Then n = arr(i, 3) '记录最大天数日期
- brr(arr(i, 3), k + 1) = brr(arr(i, 3), k + 1) + arr(i, 4) '汇总
- End If
- Next
- Next
- If n = 0 Then Exit Sub '没有数据,退出
- For i = 1 To n: brr(i, 1) = i: Next '日,赋值
- Range("A3").Resize(n, 4) = brr '写入单元格
- End Sub
复制代码 附件:
数量查询_数组.zip
(24.93 KB, 下载次数: 11)
|