PDA

View Full Version : Vlookup macro replaced with an array



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.

snb
12-23-2015, 01:11 PM
Sub M_snb()
With CreateObject("Scripting.Dictionary")
For each it in ActiveSheet.Range("A2:C5").specialcells(2)
.Item(it.value) = it.value
Next

For each it in ActiveSheet.Range("F2:G5").specialcells(2)
if .exists(it.value) then megbox .Item(it.value)
Next
End With
End Sub

There is no 'second' value

mikke3141
12-23-2015, 02:14 PM
A
B
C
D
E
F
G
H


1
Number
Name
Age


Number
Name
Age


2
1
Mat
40


3




3
2
John
32


2




4
3
Dave
28







5
4
Dick
16








The plan was to populate range(G2:H3) with Dave, 28, John and 32. Did not work

snb
12-23-2015, 02:36 PM
You might as well create a sample workbook an upload that.

What advantage should this code have compared to vlookup ?