Searching a spreadsheet

[Darren]

Hi

I have a sheet of data in Excel, and I want to be able to search 2 or 3 of
the columns for occurances of a word, that will be specified via a textbox
or similar.

I then need to take any rows where the word is found, and show these on a
seperate sheet within the workbook (ie, search results).

Any help with this would be most appreciated.

Thanks in advance,

Darren

 

[pfsardella]

Watch for linewrap. Assumes that you have a sheet named "Search
Results" to deposit the data. The following sub looks for "Hello" in
the given range and copies the results to "Search Results" worksheet.

Sub FindMe()

Dim intS As Integer
Dim rngC As Range
Dim strToFind As String, FirstAddress As String
Dim wSht As Worksheet

Application.ScreenUpdating = False

intS = 1
Set wSht = Worksheets("Search Results")
strToFind = "Hello"

With ActiveSheet.Range("A1:C2000")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
rngC.EntireRow.Copy wSht.Cells(intS, 1)
intS = intS + 1
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <>
FirstAddress
End If
End With

End Sub

-------------------------------------------------------------------------------------------------------------

Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul

 

[Darren]

Great, Thanks for help Paul.

Darren

Watch for linewrap. Assumes that you have a sheet named "Search
Results" to deposit the data. The following sub looks for "Hello" in
the given range and copies the results to "Search Results" worksheet.

Sub FindMe()

Dim intS As Integer
Dim rngC As Range
Dim strToFind As String, FirstAddress As String
Dim wSht As Worksheet

Application.ScreenUpdating = False

intS = 1
Set wSht = Worksheets("Search Results")
strToFind = "Hello"

With ActiveSheet.Range("A1:C2000")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
rngC.EntireRow.Copy wSht.Cells(intS, 1)
intS = intS + 1
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <>
FirstAddress
End If
End With

End Sub

-------------------------------------------------------------------------- -----------------------------------

Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul
-------------------------------------------------------------------------- ------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
-------------------------------------------------------------------------- ------------------------------------
End SubOn Tue, 23 Sep 2003 20:11:55 +0100, "Darren"