| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup macro that returns data from worksheet, then Loops
Hi, I have a Workbook with 2 sheets. The first sheet is called "SearchData and has 2 columns: Name Code Art 10 Ben 11 Carl 12 Doug 13 Eric 14 Fran 15 Gus 16 Henry 17 Irene 18 The second sheet is called "New" which has one column of names (o which 5 are in the previous sheet). Name Allen Bruce Carl Dana Eric Fran Gary Henry John Manually typing a formula using match and vlookup functions returns th following desired result. (Note: Search data has to be sorted and uniqu values only) The formula is (which is searching for a match and if there is, return the data in colum 2 or returns "Not Found" =IF(ISNA(MATCH(A2,source!A:A,0))=FALSE,VLOOKUP(new !$A2,source!$A$1:$B$10,2,FALSE),"No Found") Then I fill down and get the desired result. Name Code Art 10 Bruce Not Found Carl 12 Dana Not Found Eric 14 Fran 15 Gary Not Found Henry 17 John Not Found In actuality, there will be thousands of records and I would like to b able to attach this to a Button w/ a VBA Loop command with a message bo saying done when complete. Problem is I am not very good at writing cod and when I entered my formula in a module and tried to include shee names it got all screwed up. I know it has something to do with D While / Loop While the active cell in "New" is not empty. Any hel would be appreciated. Thank -- xlsxlsxl ----------------------------------------------------------------------- xlsxlsxls's Profile: http://www.excelforum.com/member.php...fo&userid=1319 View this thread: http://www.excelforum.com/showthread.php?threadid=27177 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Vlookup macro that returns data from worksheet, then Loops
You could use a loop but it would be slower and more complex.
Dim sStr as String, rng as Range sStr = "=IF(ISNA(MATCH(A2,source!A:A,0))=FALSE," & _ "VLOOKUP(new!$A2,source!$A$1" & _ ":$B$10,2,FALSE),""Not Found"")" With Worksheets("New") set rng = .range(.range("A2"), .range("A2").End(xldown)) End with rng.offset(0,1).Formula = sStr ' if you want to replace the formulas with the ' value displayed then uncomment the next line ' rng.offset(0,1).Formula = rng.offset(0,1).Value Note that since you are using False as the 4th argument of vlookup, the data in Source does not need to be sorted. The names would need to be unique, however. -- Regards, Tom Ogilvy "xlsxlsxls" wrote in message ... Hi, I have a Workbook with 2 sheets. The first sheet is called "SearchData" and has 2 columns: Name Code Art 10 Ben 11 Carl 12 Doug 13 Eric 14 Fran 15 Gus 16 Henry 17 Irene 18 The second sheet is called "New" which has one column of names (of which 5 are in the previous sheet). Name Allen Bruce Carl Dana Eric Fran Gary Henry John Manually typing a formula using match and vlookup functions returns the following desired result. (Note: Search data has to be sorted and unique values only) The formula is (which is searching for a match and if there is, returns the data in colum 2 or returns "Not Found" =IF(ISNA(MATCH(A2,source!A:A,0))=FALSE,VLOOKUP(new !$A2,source!$A$1:$B$10,2,F ALSE),"Not Found") Then I fill down and get the desired result. Name Code Art 10 Bruce Not Found Carl 12 Dana Not Found Eric 14 Fran 15 Gary Not Found Henry 17 John Not Found In actuality, there will be thousands of records and I would like to be able to attach this to a Button w/ a VBA Loop command with a message box saying done when complete. Problem is I am not very good at writing code and when I entered my formula in a module and tried to include sheet names it got all screwed up. I know it has something to do with Do While / Loop While the active cell in "New" is not empty. Any help would be appreciated. Thanks -- xlsxlsxls ------------------------------------------------------------------------ xlsxlsxls's Profile: http://www.excelforum.com/member.php...o&userid=13196 View this thread: http://www.excelforum.com/showthread...hreadid=271777 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Vlookup macro that returns data from worksheet, then Loops
First, what IS the name of your first sheet? You say "SearchData", but your
formula refers to "source". Whichever, make sure it is right in your formula. Let's give your formula a name so we can refer to it instead of writing it in code the long way to avoid syntax errors. Copy your formula and on the menu select Insert/Name/Define and paste it in the "Refers To" box. At the top type in the name FindCode and click OK. Now from the Control Toolbox menu, make a button on sheet "New". Right click the button and select View Code and put this code: Private Sub CommandButton1_Click() Range("B2").Formula = "=FindCode" Range("B2", Range("A2").End(xlDown)).Offset(0, 1).FillDown End Sub Close the code window and click the Triangle icon in the Control Toolbox menu to leave Design Mode which will activate the button. Cheers!..Mike F "xlsxlsxls" wrote in message ... Hi, I have a Workbook with 2 sheets. The first sheet is called "SearchData" and has 2 columns: Name Code Art 10 Ben 11 Carl 12 Doug 13 Eric 14 Fran 15 Gus 16 Henry 17 Irene 18 The second sheet is called "New" which has one column of names (of which 5 are in the previous sheet). Name Allen Bruce Carl Dana Eric Fran Gary Henry John Manually typing a formula using match and vlookup functions returns the following desired result. (Note: Search data has to be sorted and unique values only) The formula is (which is searching for a match and if there is, returns the data in colum 2 or returns "Not Found" =IF(ISNA(MATCH(A2,source!A:A,0))=FALSE,VLOOKUP(new !$A2,source!$A$1:$B$10,2,F ALSE),"Not Found") Then I fill down and get the desired result. Name Code Art 10 Bruce Not Found Carl 12 Dana Not Found Eric 14 Fran 15 Gary Not Found Henry 17 John Not Found In actuality, there will be thousands of records and I would like to be able to attach this to a Button w/ a VBA Loop command with a message box saying done when complete. Problem is I am not very good at writing code and when I entered my formula in a module and tried to include sheet names it got all screwed up. I know it has something to do with Do While / Loop While the active cell in "New" is not empty. Any help would be appreciated. Thanks -- xlsxlsxls ------------------------------------------------------------------------ xlsxlsxls's Profile: http://www.excelforum.com/member.php...o&userid=13196 View this thread: http://www.excelforum.com/showthread...hreadid=271777 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Vlookup macro that returns data from worksheet, then Loops
I suggest you don't use that method, but if you do, you must be cognizant of
the fact that the formula is very sensitive to the ActiveCell location. For example. E2 was the activecell when the defined name was created. ? activeCell.Address $E$2 ? activeworkbook.Names("FindCode").RefersTo =IF(ISNA(MATCH(New!A2,Source!A:A,0))=FALSE,VLOOKUP (New!$A2,Source!$A$1:$B$10 ,2,FALSE),"Not Found") ' now we make Z21 the activecell: Range("Z21").Select ? activeworkbook.Names("FindCode").RefersTo =IF(ISNA(MATCH(New!V21,Source!V:V,0))=FALSE,VLOOKU P(New!$A21,Source!$A$1:$B$ 10,2,FALSE),"Not Found") This could be made to work, but I think I have shown a simpler way. -- Regards, Tom Ogilvy "Mike Fogleman" wrote in message news:hWued.303176$D%.262609@attbi_s51... First, what IS the name of your first sheet? You say "SearchData", but your formula refers to "source". Whichever, make sure it is right in your formula. Let's give your formula a name so we can refer to it instead of writing it in code the long way to avoid syntax errors. Copy your formula and on the menu select Insert/Name/Define and paste it in the "Refers To" box. At the top type in the name FindCode and click OK. Now from the Control Toolbox menu, make a button on sheet "New". Right click the button and select View Code and put this code: Private Sub CommandButton1_Click() Range("B2").Formula = "=FindCode" Range("B2", Range("A2").End(xlDown)).Offset(0, 1).FillDown End Sub Close the code window and click the Triangle icon in the Control Toolbox menu to leave Design Mode which will activate the button. Cheers!..Mike F "xlsxlsxls" wrote in message ... Hi, I have a Workbook with 2 sheets. The first sheet is called "SearchData" and has 2 columns: Name Code Art 10 Ben 11 Carl 12 Doug 13 Eric 14 Fran 15 Gus 16 Henry 17 Irene 18 The second sheet is called "New" which has one column of names (of which 5 are in the previous sheet). Name Allen Bruce Carl Dana Eric Fran Gary Henry John Manually typing a formula using match and vlookup functions returns the following desired result. (Note: Search data has to be sorted and unique values only) The formula is (which is searching for a match and if there is, returns the data in colum 2 or returns "Not Found" =IF(ISNA(MATCH(A2,source!A:A,0))=FALSE,VLOOKUP(new !$A2,source!$A$1:$B$10,2,F ALSE),"Not Found") Then I fill down and get the desired result. Name Code Art 10 Bruce Not Found Carl 12 Dana Not Found Eric 14 Fran 15 Gary Not Found Henry 17 John Not Found In actuality, there will be thousands of records and I would like to be able to attach this to a Button w/ a VBA Loop command with a message box saying done when complete. Problem is I am not very good at writing code and when I entered my formula in a module and tried to include sheet names it got all screwed up. I know it has something to do with Do While / Loop While the active cell in "New" is not empty. Any help would be appreciated. Thanks -- xlsxlsxls ------------------------------------------------------------------------ xlsxlsxls's Profile: http://www.excelforum.com/member.php...o&userid=13196 View this thread: http://www.excelforum.com/showthread...hreadid=271777 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Vlookup macro that returns data from worksheet, then Loops
Very true, my oversight since the formula requires some relative values. The
original ActiveCell when the formula was named, must be Activated(Selected) before the code formula is used. Thanks Tom. "Tom Ogilvy" wrote in message ... I suggest you don't use that method, but if you do, you must be cognizant of the fact that the formula is very sensitive to the ActiveCell location. For example. E2 was the activecell when the defined name was created. ? activeCell.Address $E$2 ? activeworkbook.Names("FindCode").RefersTo =IF(ISNA(MATCH(New!A2,Source!A:A,0))=FALSE,VLOOKUP (New!$A2,Source!$A$1:$B$10 ,2,FALSE),"Not Found") ' now we make Z21 the activecell: Range("Z21").Select ? activeworkbook.Names("FindCode").RefersTo =IF(ISNA(MATCH(New!V21,Source!V:V,0))=FALSE,VLOOKU P(New!$A21,Source!$A$1:$B$ 10,2,FALSE),"Not Found") This could be made to work, but I think I have shown a simpler way. -- Regards, Tom Ogilvy "Mike Fogleman" wrote in message news:hWued.303176$D%.262609@attbi_s51... First, what IS the name of your first sheet? You say "SearchData", but your formula refers to "source". Whichever, make sure it is right in your formula. Let's give your formula a name so we can refer to it instead of writing it in code the long way to avoid syntax errors. Copy your formula and on the menu select Insert/Name/Define and paste it in the "Refers To" box. At the top type in the name FindCode and click OK. Now from the Control Toolbox menu, make a button on sheet "New". Right click the button and select View Code and put this code: Private Sub CommandButton1_Click() Range("B2").Formula = "=FindCode" Range("B2", Range("A2").End(xlDown)).Offset(0, 1).FillDown End Sub Close the code window and click the Triangle icon in the Control Toolbox menu to leave Design Mode which will activate the button. Cheers!..Mike F "xlsxlsxls" wrote in message ... Hi, I have a Workbook with 2 sheets. The first sheet is called "SearchData" and has 2 columns: Name Code Art 10 Ben 11 Carl 12 Doug 13 Eric 14 Fran 15 Gus 16 Henry 17 Irene 18 The second sheet is called "New" which has one column of names (of which 5 are in the previous sheet). Name Allen Bruce Carl Dana Eric Fran Gary Henry John Manually typing a formula using match and vlookup functions returns the following desired result. (Note: Search data has to be sorted and unique values only) The formula is (which is searching for a match and if there is, returns the data in colum 2 or returns "Not Found" =IF(ISNA(MATCH(A2,source!A:A,0))=FALSE,VLOOKUP(new !$A2,source!$A$1:$B$10,2,F ALSE),"Not Found") Then I fill down and get the desired result. Name Code Art 10 Bruce Not Found Carl 12 Dana Not Found Eric 14 Fran 15 Gary Not Found Henry 17 John Not Found In actuality, there will be thousands of records and I would like to be able to attach this to a Button w/ a VBA Loop command with a message box saying done when complete. Problem is I am not very good at writing code and when I entered my formula in a module and tried to include sheet names it got all screwed up. I know it has something to do with Do While / Loop While the active cell in "New" is not empty. Any help would be appreciated. Thanks -- xlsxlsxls ------------------------------------------------------------------------ xlsxlsxls's Profile: http://www.excelforum.com/member.php...o&userid=13196 View this thread: http://www.excelforum.com/showthread...hreadid=271777 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP worksheet function returns zero for empty cells | Excel Worksheet Functions | |||
| VLOOKUP returns incorrect data | Excel Worksheet Functions | |||
| Vlookup Returns Wrong/No Data | Excel Worksheet Functions | |||
| vlookup returns bad data | Excel Worksheet Functions | |||
| Fixing a vlookup macro that returns #N/A error | Excel Discussion (Misc queries) | |||
![xlsxlsxls[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
