英文:
Adding values from different rows to VBA collection
问题
我希望有更多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
基本上,我试图让宏遍历每一行并执行以下操作:
- 将采购订单号添加到集合中
- 将每个供应商货号和数量添加到相应的采购订单号中
- 存储每个供应商货号和数量在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:
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:
- Add PO Number to the collection
- Add each Vendor Item# and Qty to the corresponding PO Number
- 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
这将为你提供这种数据结构(类图):
然后,在你的代码模块中,你会将:
'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):
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论