search multiple sheets and create list from found rows

[trevorc]

Hi All,
I have a task given to me to create a search method. I have to search all sheets except for the Instruction sheet, for text entered into a search for cell on the Instructions sheet and return a list of row that have that text in them.



rough idea on how it should work
1 enter text to search for into "D4" (text to search for)
2 click a command button to start the search
3 look in all sheets except the Instruction sheet
4 return the data in the row that matches the search
5 display this in "D6:Q6" along with a reference to the location (sheet and row # in "C6")
6 only provide a maximum of 10 results.

I could try and patch this together from various bits of code I might find, but I know it would have lots of problems if I did it that way. Could somebody provide the code for this or a starting point to create my own code.

regards
Trevor

[trevorc]

Never mind I found and modified some code to do it my self.

 
Sub TextBox1_Click()
Dim ws As Worksheet, Found As Range
Dim myText As String, FirstAddress As String
Dim AddressStr As String, foundNum As Integer
myText = Worksheets("instructions").Range("K2")
If myText = "" Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
With ws
If ws.Name = "Instructions" Then GoTo myNext
Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
foundNum = foundNum + 1
AddressStr = .Name & " " & Found.Address & vbCrLf
Set Found = .UsedRange.FindNext(Found)
Found.EntireRow.Copy Destination:=Worksheets("instructions").Range("a2" & foundNum)
Worksheets("instructions").Range("A2" & foundNum) = AddressStr
ActiveSheet.Hyperlinks.Add Anchor:=Worksheets("instructions").Range("A2" & foundNum), Address:="", SubAddress:="Resistors!B5"
Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
myNext:
End With
Next ws
If Len(AddressStr) Then
MsgBox "Found: """ & myText & """ " & foundNum & " times." & vbCr & AddressStr, vbOKOnly, myText & " found in these cells"
Else:
MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
End If
End Sub