Display adjacent cell search results

[remimartin]

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

[Bob Phillips]

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

[remimartin]

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

[remimartin]

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

[Bob Phillips]

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