Find Cell Contents on Another Sheet in Workbook

[Jana]

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.

[Ian]

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" <Bauer...@gmail.com> wrote in message
news:1127412256.7...@g44g2000cwa.googlegroups.com...

[David McRitchie]

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" <Bauer...@gmail.com> wrote in message news:1127412256.7...@g44g2000cwa.googlegroups.com...

[Jana]

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

[David McRitchie]

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" <Bauer...@gmail.com> wrote in message news:1127416320.4...@g47g2000cwa.googlegroups.com...

[Jana]

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