Search Entire Workbook

[mschubert]

Hiya,

I have created a workbook with multiple worksheets of data. I'm trying to understand macros and formulas but I am not very good!

I want to create a master page which has a search box that will search all of the worksheets for any text that is entered and display the results.

is this something that can be done? If so how?

Thanks.

M

 

[CalcSux78]

This is sample code (posted by Jerry Beaucaire) that I've used in the past. In your case, you could modify the MyVal String to reference the cell your search box is located.

Option Explicit
Public MyVal As String

Sub SuperFIND()
'Author: Jerry Beaucaire
'Date: 8/13/2010
'Summary: Searches all sheets for any text string, activates when found
Dim ws As Worksheet
Dim vFIND As Range

StartOver:
MyVal = Application.InputBox("Enter the contact name to find", "Find In All Sheets", MyVal, Type:=2)
If MyVal = "False" Then Exit Sub
On Error Resume Next

For Each ws In Worksheets
	Set vFIND = ws.Cells.Find(MyVal, LookIn:=xlValues, LookAt:=xlPart)
	If Not vFIND Is Nothing Then
		ws.Activate
		vFIND.Select
		Set vFIND = Nothing
		Exit Sub
	End If
Next ws

MsgBox "Contact not found"
GoTo StartOver

End Sub

3D FIND - Jerry Beaucaire's - Excel Assistant

 

[dmt32]

Hiya,

I have created a workbook with multiple worksheets of data. I'm trying to understand macros and formulas but I am not very good!

I want to create a master page which has a search box that will search all of the worksheets for any text that is entered and display the results.

is this something that can be done? If so how?

Thanks.

M

Hi welcome to the board.

Give this code a try & see if goes in direction you are looking for.

Place code in standard module:

Sub SearchSheets()
    Dim Sh As Worksheet
    Dim Found As Range
    Dim Search As Variant
    Dim firstaddress As String, msg As String
    
    Do
    Search = InputBox("Enter Search Text", "Search")
    If StrPtr(Search) = 0 Then Exit Sub
    Loop Until Search <> ""
    
     msg = "Search: " & Search & Chr(10) & Chr(10) & "Results:" & Chr(10)
     
    For Each Sh In ThisWorkbook.Worksheets
        With Sh.UsedRange
            Set Found = .Cells.Find(What:=Search, LookIn:=xlValues, lookat:=xlWhole)
            If Not Found Is Nothing Then
                firstaddress = Found.Address
                Do
                    msg = msg & Sh.Name & Space(4) & Found.Address & Chr(10)
                        
                    Set Found = .FindNext(Found)
                Loop While Not Found Is Nothing And Found.Address <> firstaddress
            Else
                msg = msg & Sh.Name & Space(4) & "No Match" & Chr(10)
            End If
        End With
        Set Found = Nothing
    Next
    MsgBox msg, 48, "Search"
End Sub

when run, an inputbox will be displayed where you enter your search text. All worksheets in the workbook will be searched & results reported in a msgbox.

If you are new to macro's suggest that you search for VBA for beginners guides, plenty examples out there and some even free & of course, this board for any assistance needed.

Hope Helpful

Dave

 

[mschubert]

Thanks, this didn't work properly for what I need - I will try the next one.

 

[mschubert]

Hi welcome to the board.

Give this code a try & see if goes in direction you are looking for.

Place code in standard module:

Sub SearchSheets()
    Dim Sh As Worksheet
    Dim Found As Range
    Dim Search As Variant
    Dim firstaddress As String, msg As String
    
    Do
    Search = InputBox("Enter Search Text", "Search")
    If StrPtr(Search) = 0 Then Exit Sub
    Loop Until Search <> ""
    
     msg = "Search: " & Search & Chr(10) & Chr(10) & "Results:" & Chr(10)
     
    For Each Sh In ThisWorkbook.Worksheets
        With Sh.UsedRange
            Set Found = .Cells.Find(What:=Search, LookIn:=xlValues, lookat:=xlWhole)
            If Not Found Is Nothing Then
                firstaddress = Found.Address
                Do
                    msg = msg & Sh.Name & Space(4) & Found.Address & Chr(10)
                        
                    Set Found = .FindNext(Found)
                Loop While Not Found Is Nothing And Found.Address <> firstaddress
            Else
                msg = msg & Sh.Name & Space(4) & "No Match" & Chr(10)
            End If
        End With
        Set Found = Nothing
    Next
    MsgBox msg, 48, "Search"
End Sub

when run, an inputbox will be displayed where you enter your search text. All worksheets in the workbook will be searched & results reported in a msgbox.

If you are new to macro's suggest that you search for VBA for beginners guides, plenty examples out there and some even free & of course, this board for any assistance needed.

Hope Helpful

Dave

Thanks Dave - this is more along the line of what i was looking for will also have a look at those guides.

 

[dmt32]

Thanks Dave - this is more along the line of what i was looking for will also have a look at those guides.

Hi Glad suggestion helpful.

here is a well known site that offers lots of free samples that is always worth visting:http://www.contextures.com/

if you get stuck in your project development, always post your code & explain what it is you are trying to do - plenty on board here to offer help & guidance.

Dave