我偶然发现了一个我无法解决的问题。我正在尝试创建字典,例如:键:数字作为字符串项:字符串数组
当我将密钥传递给MsgBox时,一切都很好,但是当我也想添加Items时,我收到类型不匹配错误...
我的代码如下所示:
Sub test()
Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary
Dim records As String
Dim RecordArray() As String
Dim x As Integer
Application.ScreenUpdating = False
NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
Range("A1").Select
For x = 1 To NumRows
If IsNumeric(Cells(x, 1).Value) Then
records = Trim(Cells(x, 2).Value)
RecordArray() = Split(records, ",")
dict.Add Key:=(Cells(x, 1).Value), Item:=RecordArray()
ActiveCell.Offset(1, 0).Select
End If
Next x
Application.ScreenUpdating = True
Dim key As Variant
For Each key In dict.Keys
MsgBox key
MsgBox dict(key)
Next key
End Sub
数据例如:
A1:2001 B1: 0000101,0000102,0000103
A2:2015 B2: 0000107,0000108
A3:8000 B3: 0000215,0000216,0000217
等等。请注意,B列中的值以文本之前的两个空格开头。我在这里做错了什么?我知道这段代码可能不太好,但这就像我第一次尝试使用VBA :(
由于尝试将数组放入msgbox中,因此出现类型不匹配的情况。试试这个:
Sub test()
Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary
dict.CompareMode = BinaryCompare
Dim records As String
Dim RecordArray() As String
Dim x As Integer
Application.ScreenUpdating = False
NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
Range("A1").Select
For x = 1 To NumRows
If IsNumeric(Cells(x, 1).Value) Then
records = Trim(Cells(x, 2).Value)
RecordArray() = Split(records, ",")
keyString = (Cells(x, 1).Value)
dict.Add key:=keyString, Item:=RecordArray()
ActiveCell.Offset(1, 0).Select
End If
Next x
Application.ScreenUpdating = True
Dim key As Variant
For Each key In dict.Keys
MsgBox key
i = 0
For Each Item In dict(key)
MsgBox (dict(key)(i))
i = i + 1
Next Item
Next key
End Sub
我提供了一个循环来显示存储在字典中的数组的每个项目。如果只需要一个msgbox,则可以使用相同的方法构建一个字符串并显示它。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句