本帖最后由 一把小刀闯天下 于 2020-6-20 15:25 编辑
'参与一下,,,
'总金额:70868.49 , 总用量:14044.11
Option Explicit
Sub test()
Dim arr, sum(99), i, j, p, cnt, total
arr = [a1].CurrentRegion.Offset(1).Resize(, 5).Value
ReDim brr(1 To UBound(arr, 1) - 1, 1 To 1)
p = arr(UBound(arr, 1) - 1, 1)
For i = UBound(arr, 1) - 1 To 2 Step -1
If arr(i, 1) >= p Then
brr(i, 1) = Round(arr(i, 3) * arr(i, 4), 2)
cnt = cnt + arr(i, 3)
total = total + brr(i, 1)
Else
brr(i, 1) = Round(arr(i, 3) * sum(p), 2)
For j = arr(i, 1) + 1 To p
sum(j) = 0
Next
End If
sum(arr(i, 1)) = sum(arr(i, 1)) + brr(i, 1)
p = arr(i, 1)
Next
brr(1, 1) = total: arr(1, 3) = cnt
[e2].Resize(UBound(brr, 1)) = brr
MsgBox "总金额:" & total & vbNewLine & "总用量:" & cnt
End Sub |