A Function to Compare Values

[yarmulke]

Hi There!

I’ve got a basic/intermediate level question for the experts.

I have an excel Workbook, where “Sheet1” has a list of my inventor
items I’m selling in column A. “Sheet 2” has a list of the items whic
are out of stock listed in column A. I’m trying to write a functio
for Sheet1 which will input a cell, and then search Sheet2 (out o
stock) for that value, and then output “Out of Stock” or “In Stock
into the formula cell. I put a copy of the sheet at my website a
http://www.elghomearts.com/Products/Test/Book1.xls

So far, I have modified the Find action in excel and tried to get
TRUE/FALSE value out, but it isn’t working properly.

Function Finder(x)

'Compares Inventory List to Out of Stock List

Sheets("Sheet2").Select
Finder = Cells.Find(What:=x, After:=ActiveCell, LookIn:=xlFormulas
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext
MatchCase:= _
False, SearchFormat:=False).Activate
Sheets("Sheet1").Select
End Function

Can anybody point me in the right direction?

Thanks for your help.
'Book1.xls' (http://www.elghomearts.com/Products/Test/Book1.xls
http://www.elghomearts.com/Products/Test/Book1.xl

+----------------------------------------------------------------
| Attachment filename: book1[1].xls
|Download attachment: http://www.excelforum.com/attachment.php?postid=369500
+----------------------------------------------------------------

 

[gocush]

Try somethink like the following (sustituting) the names of your ranges.
List1 contains all items. List2 in the "Out of stocK" list.


Sub CheckStock()
Dim oCell As Range
For Each oCell In Range("List1")
If IsError(Application.Match(oCell, Range("List2"), 0)) Then

oCell.Offset(0, 1) = "OK"
Else
oCell.Offset(0, 1) = "Out of stock"
End If
Next
End Sub