Creating a VLookUp Function in VBA

[AaronK]

Hi all,

First time poster here, so please let me know If I'm leaving anything out.


Im trying to write a code in vba in Excel 2010 that would incorporate the vlookup function in excel.

My Data Looks like this:

<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=512><COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl66 height=20 width=64>List 1</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>Weight 1</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl66 width=64>List 2</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>Weight 2</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl66 width=64>List 3</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>Weight 3</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl66 width=64>List 4</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>Weight 4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63 height=20>a</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>2.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>j</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>20.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>a</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>14.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>p</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>2.00%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63 height=20>b</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>4.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>k</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>32.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>d</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>2.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>y</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>3.00%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63 height=20>c</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>7.50%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>e</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>10.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>g</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>14.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>u</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>6.00%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63 height=20>d</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>8.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>d</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>15.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>h</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>20.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>h</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>7.00%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63 height=20>e</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>9.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>q</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>13.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>y</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>45.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>f</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>8.00%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63 height=20>f</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>20.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>r</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>5.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>q</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>5.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>d</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>9.00%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63 height=20>g</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>35.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>n</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>5.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>t</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>10.00%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63 height=20>h</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>10.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>r</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>14.00%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63 height=20>i</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>4.50%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>w</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>15.00%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>b</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>0.18</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>Z</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>0.04</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>S</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>0.04</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63 height=20></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63 height=20>Total</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>100.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>Total</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>100.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63>Total</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>100.00%</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl63></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>100.00%</TD></TR></TBODY></TABLE>


Where I have X number of Lists, each composed of two columns. The 1st column has the name of each item in each list, and the 2nd column has the value for that item.

What I'd like to do is create a function that would let me choose two lists, and tell me the amount of items in List X and what their values are in List Y, and then total them. Also, I would like it to work the opposite way, and tell me the amount of items in List Y and what their values are in List X, and then total them.

So for example, If I wanted to look at lists 1 and 2, the function would calculate that for List 1, Items D & E are found in List 2 and have a total value of 25%. For List 2, Items D&E are found in List 1 and have a total value of 17%. For all items not in both lists, it would return values of 0.

The code I came up with so far looks like this:

Function AK_Overlap_Go(x, y)
Dim Temp(1 To 2, 2 To 1)
Dim x As Integer
Dim y As Integer
Dim i As Integer
Dim j As Integer
Dim Rng1 As Range, Rng2 As Range
'Defines Each Column with the 1st List beginning in B15
Set Rng1 = Range("B15:C15", Selection.End(x1Down)).Offset(, (x - 1) * 2)
Set Rng2 = Range("B15:C15", Selection.End(x1Down)).Offset(, (y - 1) * 2)
'Run Vlookups for Each Row
For i = 1 To LBound(Rng1)
v = WorksheetFunction.VLookup(Rng1, Rng2, 2, 0)
Next i
For j = 1 To LBound(Rng2)
v2 = WorksheetFunction.VLookup(Rng2, Rng1, 2, 0)
Next j
End Function



One of the problems I'm having is that the lists contain a different # of items, and so I think I need to loop the vlookup for each row. However, I'm not sure how to do that and get the cumulative values for each list.

Any ideas/advice would be greatly appreciated!!!!

Thanks,

-Aaron

 

[Norie]

Aaron

Could you not use worksheet formulas for this?

 

[AaronK]

Norie,

I could (and have) used worksheet formulas for this. However, I am usually dealing with lists that are sometimes 100s (if not 1000s) of rows long, and am generally comparing multiple lists at a time (5-10+).

Generally, with worksheet formulas (while entirely possible and relatively easy), I have to start from scratch each time and this can sometimes take considerable time. It would be much easier (and much less time consuming) if I could program this into a function.

-Aaron

 

[AaronK]

I've been toying around with my code, and I think I'm getting closer to where I need to be. When I run my function, it returns "#Value!", but no runtime errors in the code.


Function AKOver(x, y)
Dim i As Integer
Dim j As Integer
Dim Rng1 As Range, Rng2 As Range

'Defines Each Column with the 1st List beginning in B15
Set Rng1 = Range("B15:C15", Selection.End(x1Down)).Offset(, (x - 1) * 2)
Set Rng2 = Range("B15:C15", Selection.End(x1Down)).Offset(, (y - 1) * 2)

'Defines what the last row is in each array
FinalRow1 = Cells(Rows.Count, 2 + (x - 1) * 2).End(xlUp).Row
FinalRow2 = Cells(Rows.Count, 2 + (y - 1) * 2).End(x1Up).Row

'Begins the Vlookup Loop for the 1st List
For i = 1 To FinalRow1
v = WorksheetFunction.VLookup(Cells(15 + (i - 1), 2 + (x - 1) * 2), Rng2, 2, False)
If (IsError(v)) Then v = 0
Total1 = 0 + v
v = 0
Next i

'Begins the Vlookup Loop for the 2nd List
For j = 1 To FinalRow2
V2 = WorksheetFunction.VLookup(Cells(15 + (j - 1), 2 + (y - 1) * 2), Rng1, 2, False)
If (IsError(V2)) Then V2 = 0
total2 = 0 + V2
V2 = 0
Next j

AKOver = (Total1 + total2) / 2
End Function




Any advice/comments/insight as to why this isn't working properly would be greatly appreciated.