#1
|
|||
|
|||
search multiple sheets and create list from found rows
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 |
#2
|
|||
|
|||
Never mind I found and modified some code to do it my self.
Code:
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 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Multiple found values from five sheets, multiple returned values in sheet six? | irisha | Excel Programming | 26 | 09-30-2016 01:20 AM |
Create One List from multiple columns/sheets | tclass117 | Excel Programming | 2 | 08-23-2016 03:22 PM |
Search Multiple strings and create new word file | subodhgupta | Word | 4 | 05-22-2014 03:34 AM |
Search Multiple strings and create new word sheet | subodhgupta | Word Tables | 1 | 05-20-2014 08:09 AM |
Using numbered list in multiple rows | Bob | Word | 2 | 09-04-2012 11:09 AM |