VBA Code to Search Workbook

[joshlwilliams]

I have the following code below to search the selected sheet. I'd like to alter it to search multiple sheets in my workbook. I've tried everything I can think of and have been highly unsuccessful.

Any suggestions?


Sub FindMe()
Dim intS As Integer
Dim rngC As Range
Dim strToFind As String, FirstAddress As String
Dim wSht As Worksheet

Application.ScreenUpdating = False

intS = 1
'This step assumes that you have a worksheet named
'Search Results.
Set wSht = Worksheets("Search")
strToFind = InputBox("Enter the SIC code to find")


'Change this range to suit your own needs.
With ActiveSheet.Range("A1:G2000")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
rngC.EntireRow.Copy wSht.Cells(intS, 1)
intS = intS + 1
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
End If
End With


End Sub

 

[Smitty]

Welcome to the Board!

Are you looking for the first instance of the SIC code, or all of them?

 

[joshlwilliams]

Thanks! I've lurked for many years. I can normally search for the answer I need, but I haven't been as fortunate today.

All of them.

 

[djreiswig]

Have a look at this code.

http://www.mrexcel.com/forum/showthread.php?p=2814856#post2814856

 

[Smitty]

OK, see if this does what you want:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> FindMe()<br>    <SPAN style="color:#00007F">Dim</SPAN> intS <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rngC <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> strToFind <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, FirstAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wSht <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br><br>        intS = 1<br>        <SPAN style="color:#007F00">'This step assumes that you have a worksheet named</SPAN><br>        <SPAN style="color:#007F00">'Search Results.</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> wSht = Worksheets("Search")<br>        strToFind = InputBox("Enter the SIC code to find")<br><br><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>            <SPAN style="color:#007F00">'Change this range to suit your own needs.</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> ws.Range("A1:G2000")<br>                <SPAN style="color:#00007F">Set</SPAN> rngC = .Find(what:=strToFind, LookAt:=xlPart)<br>                    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rngC <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                        FirstAddress = rngC.Address<br>                        <SPAN style="color:#00007F">Do</SPAN><br>                            rngC.EntireRow.Copy wSht.Cells(intS, 1)<br>                            intS = intS + 1<br>                            <SPAN style="color:#00007F">Set</SPAN> rngC = .FindNext(rngC)<br>                        <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> rngC <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> And rngC.Address <> FirstAddress<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> ws<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[joshlwilliams]

Perfect!!

One last question. How could I alter it to search say 5 worksheets withing my workbook?

I'm not exactly sure of what the end user is looking for.

Thanks again!!

 

[Smitty]

Which 5 sheets? You need to be able to identify them before limited the code to them.

Usually you use the sheet name in an IF statement:

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Sheetx" and ws.Name <> "Sheety" Then
' Do stuff
End if
End with

 

[djreiswig]

Look at the link I provided, it shows how to search for something in a group of sheets...