Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching a spreadsheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching a spreadsheet
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching a spreadsheet
Great, Thanks for help Paul.
Darren wrote in message ... 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching in Excel spreadsheet | Excel Discussion (Misc queries) | |||
Searching and counting text in a spreadsheet | Excel Discussion (Misc queries) | |||
Formula for searching spreadsheet | Excel Worksheet Functions | |||
Searching a spreadsheet | Excel Discussion (Misc queries) | |||
extracting data from a spreadsheet by searching on columns | New Users to Excel |