Determine if item is in a range of items

[Mark]

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.

 

[Mike H]

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

 

[Gary''s Student]

You can try to Find the value in the range:

Sub Macro1()

Set r = Range("A14,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

 

[Mark]

Thanks

You can try to Find the value in the range:

Sub Macro1()

Set r = Range("A14,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

 

[Mark]

Thanks

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

 

[Ron Rosenfeld]

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