|
本帖最后由 dsfada 于 2015-4-15 13:02 编辑
Sub test()
Dim r%, i%
Dim arr
Dim d As Object
Dim d1 As Object
Set d = CreateObject("scripting.dictionary")
Set d1 = CreateObject("scripting.dictionary")
With Worksheets(2)
r = .Cells(.Rows.Count, 5).End(xlUp).Row
arr = .Range("a5:g" & r)
For i = 1 To UBound(arr)
d(arr(i, 3)) = arr(i, 5)
d1(arr(i, 3)) = arr(i, 6)
Next
End With
With Worksheets(1)
r = .Cells(.Rows.Count, 6).End(xlUp).Row
arr = .Range("e5:h" & r)
For i = 1 To UBound(arr)
arr(i, 3) = d(arr(i, 1))
arr(i, 4) = d1(arr(i, 1))
Next
.Range("e5").Resize(UBound(arr), UBound(arr), 2) = arr
End With
End Sub
上面代码用两个字典,能不能改成一个字典,想学习一下?
Sub test()
Dim r%, i%
Dim arr
Dim d As Object
Set d = CreateObject("scripting.dictionary")
Set d1 = CreateObject("scripting.dictionary")
With Worksheets(2)
r = .Cells(.Rows.Count, 5).End(xlUp).Row
arr = .Range("a5:g" & r)
For i = 1 To UBound(arr)
d(arr(i, 3)) = arr(i, 5) & "\\" & arr(i, 6)
Next
End With
With Worksheets(1)
r = .Cells(.Rows.Count, 6).End(xlUp).Row
arr = .Range("e5:h" & r)
For i = 1 To UBound(arr)
arr(i, 3) = Split(d(arr(i, 1), "\\"))(0)
arr(i, 4) = Split(d(arr(i, 1), "\\"))(1)
Next
.Range("e5").Resize(UBound(arr), UBound(arr), 2) = arr
End With
End Sub
|
|