Search function

S

se01rw

I appreciate that this is quite a large question but was wondering i
anyone could help. Has anyone written or know of a search functio
which will display multiple results?

What I would ideally like to be able to do is type in part of th
information within a cell and for a function/script to look for an
cells containing this information. Then display the exact contents o
any matching cells.

E.g. if a cell in a worksheet contained the names "John Smith" an
"Dave Smith" I would like to be able to type Smith into one cell an
for both "John Smith" and "Dave Smith" to be displayed elsewhere.

There must be an easy way to this that I'm just missing, Vlookup and s
on require that the exact information is typed in and even then will no
display multiple results. Apologies for length, but it's quite
specific problem
 
F

flummi

Try this ass an example and if it works as you require adapt to your
needs:

crrow = 1 'row# ofr criterion
crcol = 1 'column# of criterion
dsrow = 3 'start row# of data
derow = 20 'end row# of data
dacol = 1 'column# of data
resrow = 1 'first row# of result
rescol = 5 'column# of result
sstr = Cells(crrow, crcol).Value

k = resrow

For i = resrow To resrow + derow - dsrow
Cells(i, rescol).Value = ""
Next i
For i = dsrow To derow
dastr = Cells(i, dacol).Value
If InStr(1, dastr, sstr, 1) > 0 Then
Cells(k, rescol).Value = dastr
k = k + 1
End If
Next i

smith Mary smith
Ron Smith
Mary smith blacksmith
Peter Sellers smith
Rod Stuart
Roy black
Ron Smith
Gordon hunter
blacksmith


Hans
 
Top