Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-04-2017, 07:33 PM
trevorc trevorc is offline search multiple sheets and create list from found rows Windows 7 32bit search multiple sheets and create list from found rows Office 2013
Competent Performer
search multiple sheets and create list from found rows
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default 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
Reply With Quote
  #2  
Old 01-05-2017, 06:24 PM
trevorc trevorc is offline search multiple sheets and create list from found rows Windows 7 32bit search multiple sheets and create list from found rows Office 2013
Competent Performer
search multiple sheets and create list from found rows
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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
search multiple sheets and create list from found rows Create One List from multiple columns/sheets tclass117 Excel Programming 2 08-23-2016 03:22 PM
search multiple sheets and create list from found rows Search Multiple strings and create new word file subodhgupta Word 4 05-22-2014 03:34 AM
search multiple sheets and create list from found rows Search Multiple strings and create new word sheet subodhgupta Word Tables 1 05-20-2014 08:09 AM
search multiple sheets and create list from found rows Using numbered list in multiple rows Bob Word 2 09-04-2012 11:09 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:01 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft