|
本帖最后由 lrh788 于 2018-4-8 19:51 编辑
Sub 字典嵌套()
len1 = 6 '第一级字典的key长度
'建立字典
Lst1 = Cells(Rows.Count, 1).End(xlUp).Row
arr1 = Cells(1, 1).Resize(Lst1, 3).Value
Set dic = CreateObject("Scripting.Dictionary")
For i = 5 To Lst1
key1 = Left(arr1(i, 1), len1) & arr1(i, 2)
If dic.exists(key1) = False Then
Set dic(key1) = CreateObject("Scripting.Dictionary")
End If
dic(key1)(arr1(i, 1) & arr1(i, 2)) = arr1(i, 3)
Next i
'查找
Lst2 = Cells(Rows.Count, "e").End(xlUp).Row
arr2 = Cells(1, "e").Resize(Lst2, 3).Value
For i = 5 To Lst2
key1 = Left(arr2(i, 1), len1) & arr2(i, 2)
If dic.exists(key1) Then '前6位的字典key存在
If dic(key1).exists(arr2(i, 1) & arr2(i, 2)) Then
arr2(i, 3) = dic(key1)(arr2(i, 1) & arr2(i, 2))
Else
arr2(i, 3) = "找不到"
End If
Else
arr2(i, 3) = "找不到"
End If
Next i
Cells(1, "e").Resize(Lst2, 3).Value = arr2
Erase arr1, arr2
End Sub
我已经成功改好了,不知是否完全正确? |
|