Seaching across ALL sheets...

Like this?

Andrew Poulsom said:

Like this?

Code:

Sub Test()
    Dim What As String
    Dim sht As Worksheet
    Dim Found As Range
    Dim FirstAddress As String
    Dim Response
    What = InputBox("Search for :")
    If What = "" Then Exit Sub
    For Each sht In Worksheets
        sht.Activate
        Set Found = sht.Cells.Find(What)
        If Not Found Is Nothing Then
            FirstAddress = Found.Address
            Do
                Found.Activate
                Response = MsgBox("Continue?", vbYesNo + vbQuestion)
                If Response = vbNo Then Exit Sub
                Set Found = Cells.FindNext(After:=ActiveCell)
                If Found.Address = FirstAddress Then Exit Do
            Loop
        End If
    Next sht
    MsgBox "Search Ended!"
End Sub

Click to expand...

great, it works, thanks!

Hi Andrew

Saw this post of yours today and was wondering if it would be possible to modify the search to find part of a word, ie if you type park, it would find park and parker and spark

Thank for your time

Colin

Hello Colin, there's a LookAt argument with constants inputs: xlWhole or xlPart. It was omitted earlier in the thread.

Here's an example of xlWhole:

http://www.mrexcel.com/board2/viewtopic.php?t=69356&start=13

You can change xlWhole to xlPart.

One more question Nate,

Can the routine be limited to the first 12 sheets in the workbook

Cheers

Colin

Hello again, I believe so,

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> SrchBk()
<SPAN style="color:darkblue">Dim</SPAN> ws <SPAN style="color:darkblue">As</SPAN> Worksheet, myVar <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>, val1 <SPAN style="color:darkblue">As</SPAN> Range
<SPAN style="color:darkblue">Dim</SPAN> val2 <SPAN style="color:darkblue">As</SPAN> Range, tmp <SPAN style="color:darkblue">As</SPAN> Range, cnt <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>
myVar = InputBox("Please Enter a Search Term.")
<SPAN style="color:darkblue">If</SPAN> myVar = vbNullString <SPAN style="color:darkblue">Then</SPAN> <SPAN style="color:darkblue">Exit</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
<SPAN style="color:darkblue">For</SPAN> <SPAN style="color:darkblue">Each</SPAN> ws <SPAN style="color:darkblue">In</SPAN> Worksheets([transpose(row(1:12))])
    <SPAN style="color:darkblue">Set</SPAN> val1 = ws.Cells.Find(What:=myVar, LookIn:=xlValues, _
    lookat:=xlPart, MatchCase:=False)
    <SPAN style="color:darkblue">If</SPAN> <SPAN style="color:darkblue">Not</SPAN> val1 <SPAN style="color:darkblue">Is</SPAN> <SPAN style="color:darkblue">Nothing</SPAN> <SPAN style="color:darkblue">Then</SPAN>
        cnt = cnt + 1
        Application.Goto val1
        <SPAN style="color:darkblue">Set</SPAN> tmp = val1
again:
        <SPAN style="color:darkblue">If</SPAN> MsgBox("Seach Again?", 4) = vbNo <SPAN style="color:darkblue">Then</SPAN> <SPAN style="color:darkblue">Exit</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
        <SPAN style="color:darkblue">Set</SPAN> val2 = ws.Cells.FindNext(After:=val1)
        <SPAN style="color:darkblue">If</SPAN> val1.Address <> val2.Address And _
            tmp.Address <> val2.Address <SPAN style="color:darkblue">Then</SPAN>
            Application.Goto val2
            <SPAN style="color:darkblue">Set</SPAN> val1 = val2
            <SPAN style="color:darkblue">GoTo</SPAN> again
        <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:darkblue">Next</SPAN> ws
<SPAN style="color:darkblue">If</SPAN> <SPAN style="color:darkblue">Not</SPAN> <SPAN style="color:darkblue">CBool</SPAN>(cnt) <SPAN style="color:darkblue">Then</SPAN> MsgBox "No Matches Found"
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

Nate

Thanks a lot

Colin