Looking for cells with variable in cell text

[Tom]

Hi,

I need to search through a column of data to see if within a cell the
variable (OptionName - i.e. the 'name' or text of another cell) is
contained in that cell's text.

E.g. OptionName = General

Therefore the macro will find the cell which contains for example:

Overall Design (General)
or
General Designs
or
General (abcd)
or whatever...

This is just some of the code but i would like to be able to keep the
DO LOOP UNTIL format if possible.

Range("A5:A1000").Select

Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = OptionName Or ActiveCell.Value = 0
If ActiveCell.Value = OptionName Then
...........etc

Above code would obviously just look for cells which have the same
name as the variable. So i've tried the following combinations but
none of them work.

Loop Until ActiveCell.Value = "*" & OptionName & "*" Or
ActiveCell.Value = 0
Loop Until ActiveCell.Value = "*OptionName*" Or ActiveCell.Value = 0
Loop Until ActiveCell.Value = "*(OptionName)*" Or ActiveCell.Value = 0
Loop Until ActiveCell.Value = a & OptionName & b Or ActiveCell.Value =
0 'where a and b are "*", " ", "", etc.

Any suggestions?

Thanks in advance,

Tom

 

[Bob Phillips]

Hi Tom,

Try this

For Each cell In Range("A5:A1000")
If Evaluate("IsNumber(Search(""" & optionName & """," & _
cell.Address & "))") Then
Debug.Print cell.Address 'do it
End If
Next cell


--

HTH

RP
(remove nothere from the email address if mailing direct)