Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if item is in a range of items
I'm wanting to check if a value in one cell is in a range of other cells.
(For example, A1 contains "dog" and B1 through B10 contains a list of various animals. I want to know if "dog" in the list.) I know I can program a loop and compare item-by-item, but I'm guessing there's a couple of one-liners that will do it more efficiently, like loading the range of values in a collection then inquiring if the single cell value is in the collection. Cannot seem to find anything in Help to steer me in the right direction. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if item is in a range of items
Mark,
You don't say what you want to do if you find it so this generates a messagebox. Right click the sheet tab, view code and paste this in Sub Mersible() Dim MyRange As Range Set MyRange = Range("B1:B10") For Each c In MyRange If c.Text = Range("A1").Text Then MsgBox Range("A1").Text & " at " & c.Address Exit Sub End If Next MsgBox Range("A1").Text & " Not found " End Sub You correct about the one liner because this is easily dooable using a worksheet function:- =COUNTIF(B1:B10,A1)0 Mike "Mark" wrote: I'm wanting to check if a value in one cell is in a range of other cells. (For example, A1 contains "dog" and B1 through B10 contains a list of various animals. I want to know if "dog" in the list.) I know I can program a loop and compare item-by-item, but I'm guessing there's a couple of one-liners that will do it more efficiently, like loading the range of values in a collection then inquiring if the single cell value is in the collection. Cannot seem to find anything in Help to steer me in the right direction. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if item is in a range of items
You can try to Find the value in the range:
Sub Macro1() Set r = Range("A1:D4,G10:H11") On Error GoTo qwerty r.Find(What:="1", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate MsgBox ("it is there") Exit Sub qwerty: MsgBox ("it is not there") End Sub -- Gary''s Student - gsnu200770 "Mark" wrote: I'm wanting to check if a value in one cell is in a range of other cells. (For example, A1 contains "dog" and B1 through B10 contains a list of various animals. I want to know if "dog" in the list.) I know I can program a loop and compare item-by-item, but I'm guessing there's a couple of one-liners that will do it more efficiently, like loading the range of values in a collection then inquiring if the single cell value is in the collection. Cannot seem to find anything in Help to steer me in the right direction. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if item is in a range of items
Thanks
"Gary''s Student" wrote: You can try to Find the value in the range: Sub Macro1() Set r = Range("A1:D4,G10:H11") On Error GoTo qwerty r.Find(What:="1", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate MsgBox ("it is there") Exit Sub qwerty: MsgBox ("it is not there") End Sub -- Gary''s Student - gsnu200770 "Mark" wrote: I'm wanting to check if a value in one cell is in a range of other cells. (For example, A1 contains "dog" and B1 through B10 contains a list of various animals. I want to know if "dog" in the list.) I know I can program a loop and compare item-by-item, but I'm guessing there's a couple of one-liners that will do it more efficiently, like loading the range of values in a collection then inquiring if the single cell value is in the collection. Cannot seem to find anything in Help to steer me in the right direction. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if item is in a range of items
Thanks
"Mike H" wrote: Mark, You don't say what you want to do if you find it so this generates a messagebox. Right click the sheet tab, view code and paste this in Sub Mersible() Dim MyRange As Range Set MyRange = Range("B1:B10") For Each c In MyRange If c.Text = Range("A1").Text Then MsgBox Range("A1").Text & " at " & c.Address Exit Sub End If Next MsgBox Range("A1").Text & " Not found " End Sub You correct about the one liner because this is easily dooable using a worksheet function:- =COUNTIF(B1:B10,A1)0 Mike "Mark" wrote: I'm wanting to check if a value in one cell is in a range of other cells. (For example, A1 contains "dog" and B1 through B10 contains a list of various animals. I want to know if "dog" in the list.) I know I can program a loop and compare item-by-item, but I'm guessing there's a couple of one-liners that will do it more efficiently, like loading the range of values in a collection then inquiring if the single cell value is in the collection. Cannot seem to find anything in Help to steer me in the right direction. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if item is in a range of items
On Thu, 28 Feb 2008 05:08:01 -0800, Mark
wrote: I'm wanting to check if a value in one cell is in a range of other cells. (For example, A1 contains "dog" and B1 through B10 contains a list of various animals. I want to know if "dog" in the list.) I know I can program a loop and compare item-by-item, but I'm guessing there's a couple of one-liners that will do it more efficiently, like loading the range of values in a collection then inquiring if the single cell value is in the collection. Cannot seem to find anything in Help to steer me in the right direction. You can use the VBA FIND method: ================== Sub foo() '"Rng" is a named range on the active worksheet 'containing the list of items Const LookFor As String = "dog" Dim ItemPresent As Boolean If Range("Rng").Find(LookFor) Is Nothing Then ItemPresent = False Else ItemPresent = True End If Debug.Print LookFor, ItemPresent End Sub ============== So in your case LookFor = [A1].value and you could substitute "B1:B10" for Rng --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find all item codes for all items under one category | Excel Discussion (Misc queries) | |||
remove items from listbox together with add item | Excel Programming | |||
number of items fit into item | Excel Worksheet Functions | |||
determine if Pivot Table field item is hidden | Excel Programming | |||
How do i sort items using two rows per item in Excel? | Excel Discussion (Misc queries) |