Find text

[colinharwood]

Hi All

I have a workbook which contains 12 sheets, is it possible write a macro which will search for a text string which could be on any of the 12 sheets

Thanks a lot

colin

 

[mummbles74]

Cheers For that work Perfectly.

 

[mummbles74]

Sorry me again. My code has now been modified a few time and looks like this:

Public Sub Find_box()
Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer
Dim counter As Integer

myText = InputBox("Enter text to find")
If myText = "" Then Exit Sub
With Sheets("Equip Data")
'For Each ws In ThisWorkbook.Worksheets
'With ws
Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, MatchCase:=False)
counter = 0

If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
counter = counter + 1
Found.EntireRow.Copy _
Destination:=Worksheets("Search results").Range("A65536").End(xlUp).Offset(1, 0)
Set Found = .UsedRange.FindNext(Found)

Loop While Found.Address <> FirstAddress
End If
End With
'myNext:
'Next ws
Sheets("Search results").Select
MsgBox ("Matches Found: " & counter)
End Sub

I suspected that it wasn't pasting all the results that matched my search so I have added a counter to show how many results if finds but the number of results does not match the number of different rows that it pastes. I have counted the results manually and know that the box is giving me the correct results. I have no idea why not all the results are being pasted.

Please help. (be gentle I'm still only a beginner at this).

 

[RichMcL]

Howdy

My first post in this Forum.

I am using the code posted below and it works fine. Excel 2003, macro is in a Module.
I have 8 sheets (UNIX, UUNET, 7501...) each with about 800 rows.
Good: Is there a way to list the sheet where the results are found?
Better: Is there a way to hyperlink to each result?

Public Sub FindTextFromCell()
'Run from standard module, like: Module1.
'Coder [URL]http://www.mrexcel.com/[/URL]
 
Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer
Application.ScreenUpdating = False
 
myText = Sheets("Search").Range("B1").Value
If myText = "" Then Exit Sub
If myText = " " Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
With ws
Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, MatchCase:=False)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
If .Name = "Search" Then GoTo myNext
If .Name <> "Search" Then _
Found.EntireRow.Copy _
Destination:=Worksheets("Search").Range("A65536").End(xlUp).Offset(1, 0)
Set Found = .UsedRange.FindNext(Found)
Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
End With
myNext:
Next ws
End Sub

Thanks for the help you have given everyone on this long running question.

 

[sibsanne]

Dears,

I'm using the code displayed below.
It works like a charm.

Could we force this macro to search only into columns from A to P on every sheet?
Because I have some redundant data after the column P and those are displayed also in the search result.
So for one row I get 4 results as the same data can be found 4 time per row.

Thx for your time.

Public Sub FindTextFromCell()
'Run from standard module, like: Module1.

Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer

myText = Sheets("SEARCH").Range("D4").Value

If myText = "" Then Exit Sub

For Each ws In ThisWorkbook.Worksheets
With ws
Set Found = .UsedRange.Find(What:=myText, LookIn:=xlValues, MatchCase:=False)

If Not Found Is Nothing Then
FirstAddress = Found.Address
Do

If .Name = "SEARCH" Then GoTo myNext
If .Name <> "SEARCH" Then _
Found.EntireRow.Copy _
Destination:=Worksheets("SEARCH").Range("A65536").End(xlUp).Offset(1, 0)

Set Found = .UsedRange.FindNext(Found)

Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
End With

myNext:
Next ws
End Sub

 

[Nivlem]

Sub myfind()
Dim Message, Title, Default, SearchString
Message = "Enter your search string!" ' Set prompt.
Title = "Find ? On all sheets!" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
SearchString = InputBox(Message, Title, Default)

'SearchString = "Rob"
Set S = Sheets.Application
For Each S In Application.Sheets
With S.Range("A1:IV65536")
Set F = .Find(SearchString, MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
If F Is Nothing Then
Else
Location = F.Address
S.Select
Range(Location).Select
Exit For
End If
End With
Next S
End Sub

Great code...just what I've been trying to figure out. How can I utilize this just for the current sheet only? I have the same data on other sheets, but I just want to run the code on the current sheet only. Thank you.

 

[thejesse]

in excel 2010 this is returning only the first instance of the search term.

 

[jcrutts1]

Is there a way to hide the search box as the script flicks through results? My search box blocks half of my results.

 

[scrizo]

How do I save this code in my sheet and let others use it to search for stuff

 

[scrizo]

USING CODE FROM PAGE 3

So I got it to save, my question is, is it possible to highlight a specific cell after the result is found like i have column A-D and while what i search for is in column B I want to highlight what's in column D

i tried putting Selection.Interior.ColorIndex = 6 at the end of Set Found = .UsedRange.FindNext(Found)
and that highlights the column B what i searched for which is cool because it works