|
本帖最后由 feiaoli 于 2016-3-26 23:40 编辑
老师,D2单元格数据有效性没有和A列的添加一起变动呢?D2的数据有效性到了A35行向下就不显示日期了。我看老师的代码是到了1000行了,有效性看不出是哪里设置的,还请老师给看一下,修改一下代码!万分感激!
请只修改D2单元格的数据有效性的代码,其他代码功能不要动。谢谢!
代码如下
- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim xrng As Range
- If Target.Address <> [d2].Address Then Exit Sub
- Dim brr(1 To 2, 1 To 6)
- r1 = Range("a:a").Find("汇总", , , , , xlNext).Row '上一张表的汇总行
- r2 = Range("a:a").Find("汇总", , , , , xlPrevious).Row '下一张表的汇总行
- rq = Target '给定日期
- If rq = "全部" Then
- [b4].Resize(1, 6).Value = Cells(r2, 2).Resize(1, 6).Value
- [b5].Resize(1, 6).Value = ""
- [b6].Resize(1, 6).Value = Cells(r1, 2).Resize(1, 6).Value
- Else
- arr = Range("a8:g" & r1 - 1)
- nd = Year(rq) '年度
- For i = 1 To UBound(arr)
- xrq = arr(i, 1) '日期
- If xrq <= rq And nd = Year(xrq) Then '同年累计
- For j = 1 To 6
- brr(2, j) = brr(2, j) + arr(i, j + 1)
- If rq = xrq Then brr(1, j) = arr(i, j + 1) '本月数
- Next
- End If
- Next
- Range("b5:g6") = brr
- Set xrng = Range("a:a").Find(nd, , , lookat:=xlWhole) '全年预算
- If Not xrng Is Nothing Then [b4].Resize(1, 6).Value = Cells(xrng.Row, 2).Resize(1, 6).Value
- End If
- End Sub
复制代码
|
|