Results 1 to 2 of 2
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Any assistance would be appreciated.
    John

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •