Find Cell Contents on Another Sheet in Workbook
 

Good morning! I have a workbook with 2 sheets in it. Sheet 1 has a
list of customer account numbers in column A. Sheet 2 has a list of
customer account numbers in column B. What I want to do is iterate
through the cells in column A on sheet 1 and see if the account number
exists in column B on worksheet 2. If it does, I want to place "Y" in
column D on sheet 1 (indicating that it was found on sheet 2) in the
same row. If not, I want it to place an "N" in column D on sheet 1
(indicating that it was not found). I'm having a rough time writing
this macro and any help would be greatly appreciated.

Find Cell Contents on Another Sheet in Workbook
 

Hi Jana,
Look at the MATCH Worksheet Function.

B2: =IF(ISNA(MATCH(A2,Sheet2!B:B,0)),"N","Y")

You might also want to look at VLOOKUP if you are planning to include
other fields from another sheet.
http://www.mvps.org/dmcritchie/excel/vlookup.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jana" wrote in message oups.com...
Good morning! I have a workbook with 2 sheets in it. Sheet 1 has a
list of customer account numbers in column A. Sheet 2 has a list of
customer account numbers in column B. What I want to do is iterate
through the cells in column A on sheet 1 and see if the account number
exists in column B on worksheet 2. If it does, I want to place "Y" in
column D on sheet 1 (indicating that it was found on sheet 2) in the
same row. If not, I want it to place an "N" in column D on sheet 1
(indicating that it was not found). I'm having a rough time writing
this macro and any help would be greatly appreciated.

Find Cell Contents on Another Sheet in Workbook
 

Probably not the most elegant of solutions, but it should work. Change
Sheet1 & Sheet2 to match your sheet names. Change the range of a to suit the
rows on worksheet 1 and b to suit worksheet 2.

Sub match()
For a = 1 To 5
For b = 1 To 10
If Worksheets("Sheet1").Cells(a, 1) = Worksheets("Sheet2").Cells(b, 2)
Then
Worksheets("Sheet1").Cells(a, 2) = "Y"
End If
Next b
If Worksheets("Sheet1").Cells(a, 2) = "" Then
Worksheets("Sheet1").Cells(a, 2) = "N"
End If
Next a
End Sub

--
Ian
--
"Jana" wrote in message
oups.com...
Good morning! I have a workbook with 2 sheets in it. Sheet 1 has a
list of customer account numbers in column A. Sheet 2 has a list of
customer account numbers in column B. What I want to do is iterate
through the cells in column A on sheet 1 and see if the account number
exists in column B on worksheet 2. If it does, I want to place "Y" in
column D on sheet 1 (indicating that it was found on sheet 2) in the
same row. If not, I want it to place an "N" in column D on sheet 1
(indicating that it was not found). I'm having a rough time writing
this macro and any help would be greatly appreciated.

Find Cell Contents on Another Sheet in Workbook
 

David:
Tried your solution and it works great!!! Thanks very much :) Ian,
thanks for the input, but David's was much more concise. Appreciate
both of your speedy replies!

Jana

Find Cell Contents on Another Sheet in Workbook
 

Hi Jana,
Excellent feedback. Two different answers, you indicated which you
preferred. When I saw Ian's answer I realized that you posted in the
programming group, so it's nice to know that the simpler approach
of a worksheet function still has usefulness in the programming group
and really solved your problem.


"Jana" wrote in message oups.com...
David:
Tried your solution and it works great!!! Thanks very much :) Ian,
thanks for the input, but David's was much more concise. Appreciate
both of your speedy replies!

Jana

Find Cell Contents on Another Sheet in Workbook
 

(blush) Thanks very much, glad you liked my response!