mikke3141
12-23-2015, 12:12 PM
Hello,
I'm trying to replace my old vlookup macro with a more modern version. I first take the table into an array and then compare it to range b first column. How can I get the second element of the dictionary content. I tried ".Items(b(i, 1))(2)", but it did not work.
a = ActiveSheet.Range("A2:C5").Value
With CreateObject("Scripting.Dictionary")
For i = 1 To UBound(a, 1)
.Item(a(i, 1)) = a(i, 1)
For ii = 1 To number_of_columns
.Item(a(i, 1 + ii)) = a(i, 1 + ii)
Next ii
Next
b = ActiveSheet.Range("F2:H5").Value
For i = 1 To UBound(b, 1)
If .Exists(b(i, 1)) Then
' "How to get the second element of the existing value??"
' something like?
MsgBox .Items(b(i, 1))(2)
End If
Next
End With
I appreciate your help and Merry Christmas to you all.
I'm trying to replace my old vlookup macro with a more modern version. I first take the table into an array and then compare it to range b first column. How can I get the second element of the dictionary content. I tried ".Items(b(i, 1))(2)", but it did not work.
a = ActiveSheet.Range("A2:C5").Value
With CreateObject("Scripting.Dictionary")
For i = 1 To UBound(a, 1)
.Item(a(i, 1)) = a(i, 1)
For ii = 1 To number_of_columns
.Item(a(i, 1 + ii)) = a(i, 1 + ii)
Next ii
Next
b = ActiveSheet.Range("F2:H5").Value
For i = 1 To UBound(b, 1)
If .Exists(b(i, 1)) Then
' "How to get the second element of the existing value??"
' something like?
MsgBox .Items(b(i, 1))(2)
End If
Next
End With
I appreciate your help and Merry Christmas to you all.