Macro Help Question 1 VLOOKUP

[noloot]

Need help creating a macro that will take information entered into column B (either manually or cut and paste) look it up and place the matching value of the range in column C; if no matching value, the place N/A.

 

[MrKowz]

Welcome to the forums!

Why use a macro? Why not just use VLOOKUP?

Also, where is it trying to look at to match the value in column B?

 

[noloot]

Basically, my problem is actually more complicated, atleast to me. I have to gather a bunch of data on a daily basis; from the data I gather, I have to create several reports. One of the reports requires me to put a serial number in one column and match it with a product code in a master worksheet. So that is what I am trying to do.

 

[MrKowz]

Can you please provide some sample data as well as your desired outcome?

 

[noloot]

Before

Item Serial Product



After I enter the Item and Serial Info:

Item Serial Product
AAA 1245785
BBB 2546445
CCC 3455643

Final Product

Item Serial Product
AAA 1245785 E40-34
BBB 2546445 CD0-43
CCC 3455643 FCK-22

So what I want to do is be able to pull the data from the master file for the serial column or type it in and have the product information looked up and placed in the product column.

 

[MrKowz]

Is the product number being pulled from the master file as well? I'm still unsure what is determining the product number.

Does your data look like this?

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Item</td><td style=";">Serial</td><td style=";">Product</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">AAA</td><td style="text-align: right;;">1245785</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">BBB</td><td style="text-align: right;;">2546445</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">CCC</td><td style="text-align: right;;">3455643</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

And the table to find the product number is like this?

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Serial</td><td style=";">Product</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1245785</td><td style=";">E40-34</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2546445</td><td style=";">CD0-43</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3455643</td><td style=";">FCK-22</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />

 

[noloot]

Yes that is how the data will look; but the product number is in a completely different file. I could move the product number information to work book; but the information is still separate. So from your example, the serial number still needs to look up the product information and if none found then it needs to say N/A.

 

[MrKowz]

This what you need?

Excel 2003<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Item</TD><TD>Serial</TD><TD>Product</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD>AAA</TD><TD style="TEXT-ALIGN: right">1245785</TD><TD>E40-34</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>BBB</TD><TD style="TEXT-ALIGN: right">2546445</TD><TD>CD0-43</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>CCC</TD><TD style="TEXT-ALIGN: right">3455643</TD><TD>FCK-22</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>DDD</TD><TD style="TEXT-ALIGN: right">5414567</TD><TD style="TEXT-ALIGN: right">#N/A</TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>C2</TH><TD style="TEXT-ALIGN: left">=VLOOKUP(B2,Sheet2!$A$2:$B$4,2,0)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>




Excel 2003<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Serial</TD><TD>Product</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">1245785</TD><TD>E40-34</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">2546445</TD><TD>CD0-43</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">3455643</TD><TD>FCK-22</TD></TR></TBODY></TABLE>

Sheet2​