vba 數組計算

Sub isum()

Dim arr, Dic3, Dic4

arr = Range("A1:d11").Value 'arr數組元素按實際設定,這裏是用excel中的測試數據

Set Dic3 = CreateObject("Scripting.Dictionary")

Set Dic4 = CreateObject("Scripting.Dictionary")

For i = 1 To UBound(arr)

'字典的關鍵字,用的是序號,如果要用產品代碼就更換arr(i,1)為arr(i,2)

'甚至可以用arr(i,1) & arr(i,2)作為關鍵字

Dic3(arr(i, 1)) = Dic3(arr(i, 1)) + arr(i, 3)

Dic4(arr(i, 1)) = Dic4(arr(i, 1)) + arr(i, 4)

MsgBox Join(Dic3.keys, ",") & vbCrLf & Join(Dic3.Items, ",") & vbCrLf & Join(Dic4.keys, ",") & vbCrLf & Join(Dic4.Items, ",")

End Sub