MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Does text exist in range? (I feel silly asking, but . . .)


Posted by Bob on July 26, 2001 9:17 AM

I feel silly for asking, but I am stumped as to how to determine if a text phrase exists in a range.

Say we have cells A1 through A5 in a named range "Colors" and they have the data

red
blue
green
brown
black

I want to have a VB statement that returns True or False depending on whether the lookup matches. E.g. "yellow" would return false, but "blue" would return true.

Thanks.


Posted by faster on July 26, 2001 11:17 AM

Sub SearchRange()

Dim MySearch
MySearch = InputBox("Search String")

'or whatever your range is
Range("A1").Select

Do While Selection <> ""
If Selection = MySearch Then
MsgBox "TRUE"
Exit Sub
Else
Selection.Offset(1, 0).Select
End If
Loop

'end of loop, no match
MsgBox "FALSE"
Range("A1").Select

End Sub

Posted by Bob on July 26, 2001 11:53 AM

Thanks

Thanks. I knew that this would be pretty easy, but the code was eluding me. I'm a bit rusty with Basic.