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

 

[Aleciafxc]

Really sorry but i do not know how to run the Dim lngMyShts&

 

[Aleciafxc]

Got it thanks.

 

[jpmurph]

I just highlight all tabs then use ctrl+f find and replace all. Or you can use aspa utilities.

 

[rebelroy]

Hi all,

Can this code be tweaked to select the complete row where the item is found and still have the 'found' cell as the active cell?

Thanks

 

[Joe Was]

I think you can highlight the correct row and select the correct cell, but I dont think you can select both the row and the cell?

 

[rebelroy]

Thanks Joe,

What I have in mind is the same effect you'd get if you manually selected any range of cells - the whole range would go dark with a heavy black border but the active cell would remain white.

Is it possible to highlight the whole row and 'found' (active) cell in that manner with your code?

 

[ere674]

Hey Joe,

Great job on this. Works very well.

I hate to ask, cause it looks like you have been busy with this, but I was curious about a bit of tweaking:

1. First tweak I am curious about is location of the message boxes - is there a way to have them pop up at the right side instead of the middle?

2. I was hoping there is a method of avoiding certain areas within the workbook (like not searching a certain column, lets say.)

3. Is there a way to add a hyperlink or a choice to the last message box that states "Found 5 items." I am looking to pick one of those items to go to.

I am using the code from pg 3 (I have already added the 'cancel' feature as discussed earlier in this thread.)

Thanks very much for your work.

'Cheers'

ERE

 

[Joe Was]

Yes, you can, but you will need to replace the MsgBox code with UserForm code to do it all!

You can re-position some types of MsgBox's, but a good solution including all you want to do will need custom UserForms.

 

[mummbles74]

Hi hope you can help this is my first post. I have written this code (using the previous posts for help) all works fine but I want it to only work on one sheet called "Equip Data" any ideas how I stop it searching through the rest of the sheets?

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
myText = InputBox("Enter text to find")
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 results" Then GoTo myNext
If .Name <> "Search results" Then _
Found.EntireRow.Copy _
Destination:=Worksheets("Search results").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
Sheets("Search results").Select
End Sub

Hope someone can help

cheers

 

[jeffreybrown]

This seems to work...

As you can see all I did was comment out the portion not required.

Public Sub Find_box()
'Dim ws As Worksheet
Dim Found As Range
Dim rngNm As String
Dim myText As String
Dim FirstAddress As String
Dim thisLoc As String
Dim AddressStr As String
Dim foundNum 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)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
If .Name = "Search results" Then GoTo myNext
If .Name <> "Search results" Then _
Found.EntireRow.Copy Destination:=Worksheets("Search results").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
Sheets("Search results").Select
End Sub