Search (XL97;SR2)

Is it possible to do a search in one range and then put the results in another range. For example: Range 1 contains a list of names, the search would be for *ste* and the results would be placed in Range 2. Note the astricks in the text of the search before and after the text.

Re: Search (XL97;SR2)

You could do something like this:

<pre>Dim oCell As Range
Dim I As Long, strAddress As String
Worksheets("Sheet2").Range("A:A").ClearContents
With Worksheets("Sheet1").Range("A:A")
I = 0
Set oCell = Nothing
On Error Resume Next
Set oCell = .Find(What:="*ste*")
On Error GoTo 0
If Not oCell Is Nothing Then
strAddress = oCell.Address
Do
Worksheets("Sheet2").Range("A1").Offset(I, 0).Value = oCell.Value
I = I + 1
On Error Resume Next
Set oCell = .FindNext(After:=oCell)
On Error GoTo 0
Loop While (Not oCell Is Nothing) And (strAddress <> oCell.Address)
End If
End With
</pre>