Creating a VLookUp Function in VBA

AaronK

New Member
Joined
Dec 28, 2011
Messages
6
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Aaron

Could you not use worksheet formulas for this?
 

AaronK

New Member
Joined
Dec 28, 2011
Messages
6
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

New Member
Joined
Dec 28, 2011
Messages
6
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.
 

Forum statistics

Threads
1,082,360
Messages
5,364,931
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top