vba - VBA字典和多项汇总(VBA Dictionary and multiple items aggregation)

Question

Im having issues with a dictionary.

(我的字典有问题。)

I have multiple invoices from the same customer and i want to sum them up.

(我有同一位客户的多张发票，我想对它们进行汇总。)

Therefore my Key is customer name and i want the Item to be sum of the invoices per customer.

(因此，我的密钥是客户名称，并且我希望项目是每个客户的发票总和。)

I would also like to add a second Item which would be payments, that would also be summed up per customer.

(我还想添加第二个项目，即付款，也将对每个客户进行汇总。)

Is there a way to do this?

(有没有办法做到这一点？)

Ive been trying to sum the invoices below, but so far i'm only able to save the invoices to the dictionary.

(香港专业教育学院一直试图总结下面的发票，但到目前为止，我只能将发票保存到字典中。)

In the optimal situation, i would have customer name in column A, sum of invoices in column B and sum of payments in column C.

(在最佳情况下，我在A列中有客户名称，在B列中有发票的总和，在C列中有付款的总和。)

ps.

(ps。)

Pivot is not an option.

(枢轴不是一个选择。)

Thank you for the assistance!

(感谢您的帮助！)

Sub Customers()

Dim lr1 As Long
Dim lr2 As Long
Dim x As Long, y As Long, n As Integer
Dim arr As Variant
Dim rng As Range, cl As Range
Dim Dict As New Dictionary

    'Define range

    With Blad6
        lr1 = Worksheets("ww").Cells(.Rows.Count, 1).End(xlUp).Row
        arr = Worksheets("ww").Range("A2:A" & lr1 + 1)
    End With

    For x = 2 To UBound(arr)
    If Dict.Exists(Worksheets("ww").Cells(x, 2).Value) Then
        Dict(Cells(x, 1).Value) = Dict(Worksheets("ww").Cells(x, 2).Value) + Cells(x, 1).Offset(0, 6).Value
        Else
        Dict.Add Worksheets("ww").Cells(x, 1).Value, Cells(x, 1).Offset(0, 6).Value

    End If

    Debug.Print Dict(Cells(x, 1).Value)

    Next

End Sub

  ask by Carlsberg789 translate from so

Answer 1

It's doable through custom class type.

(通过自定义类类型是可行的。)

First you create the class named clsINVOICE

(首先，您创建名为clsINVOICE的类)

Option Explicit

Private invvalue    As Double
Private invterm     As Integer

Public Property Let VALUE(ByVal dblvalue As Double)
    invvalue = dblvalue
End Property
Public Property Get VALUE() As Double
    VALUE = invvalue
End Property

Public Property Let TERM(ByVal intValue As Integer)
    invterm = intValue
End Property
Public Property Get TERM() As Integer
    TERM = invterm
End Property

Then you can use it to collect in your dictionary, like egbelow

(然后，您可以使用它来收集您的字典，例如下面)

Sub UserTypeDictionary()

    Dim Dict        As New Dictionary
    Dim myinvoice   As New clsINVOICE

    myinvoice.VALUE = 12.34
    myinvoice.TERM = 14

    Dict.Add "myInvoiceNumber", myinvoice

    Debug.Print Dict("myInvoiceNumber").VALUE
    Debug.Print Dict("myInvoiceNumber").TERM

End Sub