|
可自主指定汇总代码位数的金额,仅供参考:
Sub 汇总指定位数代码的金额()
Dim i As Long
Dim arr As Variant
Dim je As String
Dim d As Object
Dim totalAmounts As Double
Dim code As String
Range("R:S").Clear '清除R:S列所有数据及格式
Set d = CreateObject("scripting.dictionary")
With Worksheets("K本费")
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
arr = .Range("A2:G" & lastRow).Value
n = Cells(2, "P") '将P2单元格的值(即设定的汇总代码位数)赋给n
For i = 1 To UBound(arr)
If Len(arr(i, 5)) >= n Then '只处理指定位数及以上的代码
je = Left(arr(i, 5), n) '撮取指定位数代码
totalAmounts = 0
code = ""
If Not d.Exists(je) Then
d.Add je, totalAmounts
End If
d(je) = d(je) + arr(i, 7) '对指定位数代码的金额进行汇总
End If
Next i
With .Range("R2").Resize(d.Count, 2)
.Value = Application.Transpose(Array(d.Keys, d.Items))
.NumberFormat = "@"
.Offset(-1, 0).Resize(1, 2).Value = Array("代码", "原币金额码")
.Parent.Columns("R:S").AutoFit
.Parent.Rows.AutoFit
.Parent.Range("R1:S1").Font.Bold = True
End With
[R1].CurrentRegion.Borders.LineStyle = xlContinuous
Range("R1:S1").HorizontalAlignment = xlCenter
.Columns("S").NumberFormat = "0.00"
Range("R1:S1").Interior.Color = RGB(223, 235, 253)
End With
End Sub
|
-
|