|
本帖最后由 wayy 于 2012-7-27 16:05 编辑
- Function total(Rng As Range, fl As Long)
- Dim i&, arr, Dic1, Dic2
- Dim SD As Date, ED As Date
- Dim Str As String
- SD = Range("c2")
- ED = Range("e2")
- Set Dic1 = CreateObject("Scripting.Dictionary")
- Set Dic2 = CreateObject("Scripting.Dictionary")
- With Sheet1
- arr = .Range("a2", .[e65536].End(xlUp))
- End With
- For i = 2 To UBound(arr)
- If arr(i, 1) >= SD And arr(i, 1) <= ED Then
- Dic1(arr(i, 2)) = Dic1(arr(i, 2)) + arr(i, 5)
- End If
- If arr(i, 1) <= ED Then
- Dic2(arr(i, 2)) = Dic2(arr(i, 2)) + arr(i, 5)
- End If
- Next i
- Str = Rng.Value
- If fl = 1 Then
- total = Dic1(Str)
- ElseIf fl = 2 Then
- total = Dic2(Str)
- End If
- End Function
复制代码 使用方法:=total(a4,1) 是取区间值 =total(a4,2)是取前期累计值。
原来的错误是出在数组了。CurrentRegion这里的原因。 |
|