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

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

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

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

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