将不同行的值添加到VBA集合中

huangapple go评论85阅读模式
英文:

Adding values from different rows to VBA collection

问题

我希望有更多VBA知识的人能教我如何调整我的代码以获得我期望的结果。让我从我将要使用的数据开始:

将不同行的值添加到VBA集合中

采购订单号 供应商货号 数量
MO-0021244 NFLPACK8D 1
MO-0021244 NFLPACK9D 1
MO-008512 NFLPACK8M 1
MO-965403 NFLPACK9 1
MO-780066 SOCLAM1 1

以及当前的代码(类模块):

Public Key As String
Public SKU1 As String
Public Quantity1 As Long
Public SKU2 As String
Public Quantity2 As Long
Public SKU3 As String
Public Quantity3 As Long
Public SKU4 As String
Public Quantity4 As Long
Public SKU5 As String
Public Quantity5 As Long
Public ItemList As Collection
Private Sub Class_Initialize()
    SKU1 = 0
    Quantity1 = 0
    SKU2 = 0
    Quantity2 = 0
    SKU3 = 0
    Quantity3 = 0
    SKU4 = 0
    Quantity4 = 0
    SKU5 = 0
    Quantity5 = 0
    Set ItemList = New Collection
End Sub

(模块)

Public Sub POClass()

Dim col As Collection
Dim dataItems As cItems
Dim itemKey As String
Dim item1 As String
Dim item2 As Long
Dim ws As Worksheet
Dim r As Long

Set ws = ActiveSheet
Set col = New Collection

Dim lastRow As Long
lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row

For r = 2 To lastRow
    itemKey = CStr(ws.Cells(r, "A").Value2)
    item1 = CStr(ws.Cells(r, "M").Value2)
    item2 = CLng(ws.Cells(r, "N").Value2)

    '检查键是否已经存在
    Set dataItems = Nothing: On Error Resume Next
    Set dataItems = col(itemKey): On Error GoTo 0

    '如果键不存在,创建一个新的类对象
    If dataItems Is Nothing Then
        Set dataItems = New cItems
        dataItems.Key = itemKey
        col.Add dataItems, itemKey
    End If

    '将单元格值添加到类对象
    With dataItems
        .SKU = item1
        .Quantity = item2
        .ItemList.Add item1
        .ItemList.Add item2
    End With

Next

End Sub

基本上,我试图让宏遍历每一行并执行以下操作:

  1. 将采购订单号添加到集合中
  2. 将每个供应商货号和数量添加到相应的采购订单号中
  3. 存储每个供应商货号和数量在SKU1-5和Quantity 1-5中

每个采购订单可能只有一个项目或多达5个。我是否做得对,或者有没有更好的方法?如果一个采购订单中有多个项目,我不知道如何将SKU和数量添加到同一个采购订单号中。

所以预期的输出将会是:

键:MO-0021244
SKU1:NFLPACK8D
数量1:1
SKU2:NFLPACK9D
数量2:1

键:MO-008512
SKU1:NFLPACK8M
数量1:1

键:MO-965403
SKU1:NFLPACK9
数量1:1

键:MO-780066
SKU1:SOCLAM1
数量1:1

英文:

I'm hoping someone with more VBA knowledge than me can teach me how to adjust my code to get the outcome I'm looking for. Let me start with the data I'll be using:

将不同行的值添加到VBA集合中

PO Number Vendor Item # Qty
MO-0021244 NFLPACK8D 1
MO-0021244 NFLPACK9D 1
MO-008512 NFLPACK8M 1
MO-965403 NFLPACK9 1
MO-780066 SOCLAM1 1

And current code (Class Module):

Public Key As String
Public SKU1 As String
Public Quantity1 As Long
Public SKU2 As String
Public Quantity2 As Long
Public SKU3 As String
Public Quantity3 As Long
Public SKU4 As String
Public Quantity4 As Long
Public SKU5 As String
Public Quantity5 As Long
Public ItemList As Collection
Private Sub Class_Initialize()
    SKU1 = 0
    Quantity1 = 0
    SKU2 = 0
    Quantity2 = 0
    SKU3 = 0
    Quantity3 = 0
    SKU4 = 0
    Quantity4 = 0
    SKU5 = 0
    Quantity5 = 0
    Set ItemList = New Collection
End Sub

(Module)

Public Sub POClass()

Dim col As Collection
Dim dataItems As cItems
Dim itemKey As String
Dim item1 As String
Dim item2 As Long
Dim ws As Worksheet
Dim r As Long

Set ws = ActiveSheet
Set col = New Collection

Dim lastRow As Long
lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row

For r = 2 To lastRow
    itemKey = CStr(ws.Cells(r, "A").Value2)
    item1 = CStr(ws.Cells(r, "M").Value2)
    item2 = CLng(ws.Cells(r, "N").Value2)

    'Check if key already exists
    Set dataItems = Nothing: On Error Resume Next
    Set dataItems = col(itemKey): On Error GoTo 0

    'If key doesn't exist, create a new class object
    If dataItems Is Nothing Then
        Set dataItems = New cItems
        dataItems.Key = itemKey
        col.Add dataItems, itemKey
    End If

    'Add cell values to the class object
    With dataItems
        .SKU = item1
        .Quantity = item2
        .ItemList.Add item1
        .ItemList.Add item2
    End With

Next

End Sub

Essentially, I'm trying to have the macro go through each row and do the following:

  1. Add PO Number to the collection
  2. Add each Vendor Item# and Qty to the corresponding PO Number
  3. Store each Vendor Item# and Qty in SKU1-5 and Quantity 1-5

Each PO might have as little as 1 item or up to 5. Am I doing this correctly or is there a better way? I can't figure out how to get SKUs/Quantities added to the same PO number if there are multiple for one PO.

So the expected output would be:

Key: MO-0021244
SKU1: NFLPACK8D
Quantity1: 1
SKU2: NFLPACK9D
Quantity2: 1

Key: MO-008512
SKU1: NFLPACK8M
Quantity1: 1

Key: MO-965403
SKU1: NFLPACK9
Quantity1: 1

Key: MO-780066
SKU1: SOCLAM1
Quantity1: 1

答案1

得分: 1

你可以使用字典而不是集合来更容易地存储SKU和数量,当你有很多可能的值时,这种方法更具可扩展性。这样,你完全可以避免定义带有索引的变量(Quantity1、Quantity2、Quantity3等)。

你的类模块将会是这样的:

Public Key As String
Public ItemList As Dictionary '需要 Microsoft Scripting Runtime Library (Tools > References... > Add "Microsoft Scripting Runtime" > Press OK)
Private Sub Class_Initialize()
    Set ItemList = New Dictionary
End Sub

这将为你提供这种数据结构(类图):

将不同行的值添加到VBA集合中

然后,在你的代码模块中,你会将:

    'Add cell values to the class object
    With dataItems
        .sku = item1
        .Quantity = item2
        .ItemList.Add item1
        .ItemList.Add item2
    End With

替换为

    'Add cell values to the class object
    dataItems.ItemList.Add item1, item2

然后,你可以循环遍历这些值,并按你在问题中提到的方式返回输出,就像这样:

Dim cItem As cItems
For Each cItem In col
    
    Dim qtyDetails As String
    qtyDetails = vbNullString

    Dim sku As Variant
    For Each sku In cItem.ItemList.Keys
        qtyDetails = qtyDetails & " SKU: " & sku & " Quantity: " & cItem.ItemList(sku)
    Next sku

    Debug.Print "Key: " & cItem.Key & " " & qtyDetails

Next

另外,请注意,你可以避免使用类,而使用嵌套字典。

英文:

You could use a dictionary instead of a collection to make it easier to store the SKU and quantities together and this approach scales better when you have a lot of possible values. You completely avoid having to define variables with an index (Quantity1, Quantity2, Quantity3, etc.).

Your class module would then be:

Public Key As String
Public ItemList As Dictionary 'Requires Microsoft Scripting Runtime Library (Tools > References... > Add "Microsoft Scripting Runtime" > Press OK)
Private Sub Class_Initialize()
    Set ItemList = New Dictionary
End Sub

Which would give you this data structure (class diagram):

将不同行的值添加到VBA集合中

Then, in your code module, you'd replace :

    'Add cell values to the class object
    With dataItems
        .sku = item1
        .Quantity = item2
        .ItemList.Add item1
        .ItemList.Add item2
    End With

with

    'Add cell values to the class object
    dataItems.ItemList.Add item1, item2

Then you can loop over the values and return the output you mentioned in your quesiton like this :

Dim cItem As cItems
For Each cItem In col
    
    Dim qtyDetails As String
    qtyDetails = vbNullString

    Dim sku As Variant
    For Each sku In cItem.ItemList.Keys
        qtyDetails = qtyDetails & " SKU: " & sku & " Quantity: " & cItem.ItemList(sku)
    Next sku

    Debug.Print "Key: " & cItem.Key & " " & qtyDetails

Next

Also note that you could avoid using a class and use a nested dictionary instead.

huangapple
  • 本文由 发表于 2023年6月15日 03:34:09
  • 转载请务必保留本文链接:https://go.coder-hub.com/76476998.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定