Consulting

Results 1 to 5 of 5

Thread: Display adjacent cell search results

  1. #1

    Unhappy Display adjacent cell search results

    Here is my code. It searches column A for keyword inputs. But, I what the program to display the adjacent column cell results on a particular sheet not a msgbox. Please help



    Sub FindStrings()
    Dim firstCell, nextCell, stringToFind As String
    ' Show an input box and return the entry to a variable.
    stringToFind = _
    Application.InputBox("Enter Keyword", "Search Treatment")
    ' Set an object variable to evaluate the Find command.
    Set firstCell = Cells.Find(what:=stringToFind, lookat:=xlPart, _
    searchdirection:=xlPrevious)
    ' If the string is not found, show this message box.



    If firstCell Is Nothing Then
    MsgBox "Search Value Not Found.", vbExclamation
    Else
    ' Otherwise, find the next occurrence of the search text.
    nextCell = _
    (Cells.FindNext(after:=Range(firstCell.Address)).Value
    ' Show its address in a message box.
    MsgBox nextCell
    ' Continue finding the next occurrence as long as the address of
    ' the found cell is not the same as the first cell.
    Do While firstCell.Address <> nextCell
    nextCell = Cells.FindNext(after:=Range(nextCell)).Address
    MsgBox nextCell
    Loop
    End If
    End Sub

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    [vba]

    Sub FindStrings()
    Dim FirstCell As Range, NextCell As Range
    Dim stringToFind As String
    Dim NextRow As Long

    ' Show an input box and return the entry to a variable.
    stringToFind = _
    Application.InputBox("Enter Keyword", "Search Treatment")
    ' Set an object variable to evaluate the Find command.
    Set FirstCell = Cells.Find(what:=stringToFind, _
    lookat:=xlPart, _
    searchdirection:=xlPrevious)
    ' If the string is not found, show this message box.

    NextRow = 0
    If FirstCell Is Nothing Then
    MsgBox "Search Value Not Found.", vbExclamation
    Else
    Set NextCell = FirstCell
    Do
    ' Otherwise, find the next occurrence of the search text.
    Set NextCell = _
    Cells.FindNext(NextCell)
    If Not NextCell Is Nothing And _
    FirstCell.Address <> NextCell.Address Then

    NextRow = NextRow + 1
    Worksheets("Other Sheet").Cells(NextRow, "A").Value = NextCell.Offset(0, 1).Value
    End If
    Loop Until NextCell Is Nothing Or _
    FirstCell.Address = NextCell.Address
    End If
    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Thats great. In your do statement how can I display not only the next column by the next two columns? thx

  4. #4
    Also the search is not searching all the data. When I replace an entry further down with an entry further above, I ran the program and it picked out the one above not the entry I place further down

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    [vba]

    Sub FindStrings()
    Dim FirstCell As Range, NextCell As Range
    Dim stringToFind As String
    Dim NextRow As Long

    ' Show an input box and return the entry to a variable.
    stringToFind = _
    Application.InputBox("Enter Keyword", "Search Treatment")
    ' Set an object variable to evaluate the Find command.
    Set FirstCell = Cells.Find(what:=stringToFind, _
    lookat:=xlPart, _
    searchdirection:=xlPrevious)
    ' If the string is not found, show this message box.

    NextRow = 0
    If FirstCell Is Nothing Then
    MsgBox "Search Value Not Found.", vbExclamation
    Else
    Set NextCell = FirstCell
    Do
    ' Otherwise, find the next occurrence of the search text.
    Set NextCell = _
    Cells.FindNext(NextCell)
    If Not NextCell Is Nothing And _
    FirstCell.Address <> NextCell.Address Then

    NextRow = NextRow + 1
    Worksheets("Other Sheet").Cells(NextRow, "A").Value = NextCell.Offset(0, 1).Value
    Worksheets("Other Sheet").Cells(NextRow, "B").Value = NextCell.Offset(0, 2).Value
    End If
    Loop Until NextCell Is Nothing Or _
    FirstCell.Address = NextCell.Address
    End If
    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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