Searching Words in a String

[iyerarunkuma]

In an Excel Application, I will take a input from a user. The input can be in the form of a single word or a string. This value has to be matched in the Excel Workbook Sheet1, ColumnA and if a match is found for any of the Words, that row should be copied and pasted on Sheet2.

For Example
The User searches for "Value added Reports". Now the Application should search for all these 3 words in Column A and if a match is found, it should populate the entire row in Sheet2. It will continue till the entire workbook has been searched.

[Bob Phillips]

Lookup Find and FindNext in VBA help, it does what you want, apart from the copying, and that is handled like so

[vba]

ActiveCell.EntireRow.Copy Worksheets("Sheet2").Cells(NextRow,"A")
NextRow = NextRow + 1
[/vba]

[iyerarunkuma]

I have to take an input from the user and search based on the input.

I am sorry I am not that strong in VBA. I dont see a statement where you accept input from a user and then search based on that criteria

[lucas]

if a match is found for any of the Words, that row should be copied and pasted on Sheet2.

This may be stretching the capability of find.....someone tell me if I'm wrong.

[lucas]

This seems to work on sheet 2 from a search term from an input box. I wouldn't think it would work on multiple words at once though. It does use xlpart so any part of the term will be found.
[vba]Option Explicit
Sub FindCodes()
Dim rngToSearch As Range
Dim cel1 As Range
Dim c As Range
Dim counter As Integer
Dim firstAddress As String
Dim MyInput As String
Sheets("Output Sheet").UsedRange.ClearContents
counter = 1 'start output in row 1
MyInput = InputBox("Search for String...", "Search", "Enter your search sting here")
Set rngToSearch = Sheets("Sheet2").Columns(1)
Set c = rngToSearch.Find(What:=MyInput, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Copy Worksheets("Output Sheet").Rows(counter)
counter = counter + 1
Set c = rngToSearch.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
Sheets("Output Sheet").Select
End Sub

[/vba]
Edit: I updated this code and attachment to add the clear used range on the output sheet in case your search term doesn't overwrite old data.