Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Function to Compare Values
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 +---------------------------------------------------------------- ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Function to Compare Values
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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare values on sheet 1 to values on sheet2 | Excel Worksheet Functions | |||
Excel Compare values in columns & display missing values in a new | Excel Discussion (Misc queries) | |||
Compare Listbox values with Collection values | Excel Programming | |||
compare values between workbooks and copy values | Excel Programming |