Add Range to Dictionary

[30percent]

Hi,

how do I add a range to dictionary using VBA? As an example shown below, using Product as key and Order as value:

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Product[/TD]
[TD="class: xl63, width: 64"]Order[/TD]
[/TR]
[TR]
[TD="class: xl64"]Chair[/TD]
[TD="class: xl64"]10[/TD]
[/TR]
[TR]
[TD="class: xl64"]Table[/TD]
[TD="class: xl64"]5[/TD]
[/TR]
[TR]
[TD="class: xl64"]Pencil[/TD]
[TD="class: xl64"]20[/TD]
[/TR]
[TR]
[TD="class: xl64"]Pen[/TD]
[TD="class: xl64"]30[/TD]
[/TR]
[TR]
[TD="class: xl64"]Printer[/TD]
[TD="class: xl64"]1[/TD]
[/TR]
[TR]
[TD="class: xl64"]Phone[/TD]
[TD="class: xl64"]5[/TD]
[/TR]
</tbody>[/TABLE]

Thank you!

 

[JohnExcel222]

Hi Rick, what is the best way to lookup values in vba (as an alternative to xlookup in excel)..

Is it possible to use arrays as an alternatibe to vlookup/xlookup?

 

[Rick Rothstein]

To look up values in an array, you would run a loop. Because the array is in memory, looping will occur quite quickly.

No, Excel formulas do not have direct access to VBA arrays. You could give formulas access via a UDF (user defined function) but once you are working in VBA, my recommendation is to continue working in VBA and output your results from there (that should be more efficient overall).