Fixing code for search engine for whole workbook

**brownie4321** · 06-01-2012, 07:23 AM

Employee Phone Contacts List.xlsm

Attached is the file I am working on.

I have a code half written and it performs quite well (so far). My language is correct to search the entire workbook - every cell in every sheet. For some reason when I enter the search parameters for a cell not in column A it returns not found. Why? Also, when returned a not found message, it returns a message for every sheet. I have to click OK once for every sheet.

I am also at a loss as to how to write the code for various types of phone numbers. This list will be used by more than just myself and I have no control over how others may enter information. Phone numbers can be listed in may ways. What does the code need to say to allow for the periods, dashes, spaces, no spaces, etc?

Any help will be appreciated. Thanks in advance!

**royUK** · 06-01-2012, 07:48 AM

Why split the database into multiple sheets?

**brownie4321** · 06-02-2012, 12:38 AM

Ths is for the county dispatch center I work in. The reason is for printing purposes. If we ever needed to print the contact information for a certain group of people it may be easier if they are separated. I am fairly good with excel, new to VBA and macros. No one else in our office is very good with excel. I need to make this as simple as possible for all of our dispatchers. Currently we use a three ring binder and it takes forever to find contact info. More often than not, we need this information very quickly. And since I work for the county, there are no monies to purchase a Db program to do this for us. It needs to be created. So, here I am!

**royUK** · 06-02-2012, 02:39 AM

All the data should be on one sheet, with a column for groups. The data can then be filtered by groups for printing

**brownie4321** · 06-03-2012, 12:38 AM

Employee Phone Contacts List - Copy - Copy.xlsm

Here is the file with all numbers and references on a single sheet. I still can't get it to search the entire sheet. I still don't know how to modify the code to search for numbers with different formatting. Example in first post.

**protonLeah** · 06-03-2012, 01:35 AM

This this work for you?

Option Explicit
Sub MyFind()
    Dim Message         As String, _
        Title           As String, _
        Default         As String, _
        SearchString    As String, _
        Location        As String, _
        Results         As Variant, _
        WS              As Worksheet
    
    Message = "Enter your query!" ' Set prompt.
    Title = "What are you looking for?" ' Set title.
    Default = "" ' Set default.
    
    SearchString = Trim(InputBox(Message, Title, Default))
    
    For Each WS In ThisWorkbook.Sheets
        Set Results = WS.Range("A1").CurrentRegion _
            .Find(SearchString, _
                MatchCase:=False, _
                LookAt:=xlPart, _
                LookIn:=xlValues)
                
        If Not Results Is Nothing Then
            Location = Results.Address(0, 0)
            WS.Select
            Range(Location).Select
            Exit Sub
        End If
    Next WS
    MsgBox Chr(34) & SearchString & Chr(34) & vbCrLf _
    & "was not found", vbCritical + vbOKOnly
End Sub

**brownie4321** · 06-03-2012, 01:40 AM

Works Great. Thanks! What about if there is more than one result to the query? Is there a way to add a 'find all' or a 'next' tab?

**protonLeah** · 06-03-2012, 01:49 AM

You will need to modify the code to loop with the FINDNEXT function. But, you are doing a cell selection after a successful find, so what would you want if there were more than one result?

**brownie4321** · 06-03-2012, 01:53 AM

Is it possible to have the cells copied from their original location (temporarily) to the search page withh all the possible results? Or would it be easier to have another dialog box pop up with a button stating 'click for next result' or something like that?

**protonLeah** · 06-03-2012, 09:55 PM

an alternative:

Option Explicit
Sub MyFind()
    Dim Message         As String, _
        Title           As String, _
        Default         As String, _
        SearchString    As String, _
        Location        As String, _
        Report          As String, _
        Results         As Range, _
        WS              As Worksheet, _
        NotFoundCount   As Long, _
        Ndx             As Variant, _
        temp            As String
    
    Message = "Enter your query!" ' Set prompt.
    Title = "What are you looking for?" ' Set title.
    Default = "" ' Set default.
    temp = "hhhhhhhhhhhhhhh"
    Set WS = Sheets("list")
    
    SearchString = Trim(InputBox(Message, Title, Default))
    
        With WS.Range("A1").CurrentRegion
            Set Results = .Find(SearchString, _
                            MatchCase:=False, _
                            LookAt:=xlPart, _
                            LookIn:=xlValues)
                
            If Not Results Is Nothing Then
                Location = Results.Address(0, 0)
                Do
                    For Each Ndx In Array("B", "A", "H", "I", "J", "K")
                        RSet temp = .Cells(Results.Row, Ndx).Value
                        Default = Default & IIf(Default <> "", vbTab, "") & temp
                    Next Ndx
                    Report = Report & IIf(Report <> "", vbCrLf, "") & Default
                    Default = ""
                   
                    Set Results = .FindNext(Results)
                Loop While Not Results Is Nothing And Results.Address(0, 0) <> Location
            Else
                NotFoundCount = NotFoundCount + 1
            End If
        End With
    
    If NotFoundCount = ThisWorkbook.Sheets.Count Then
        MsgBox Chr(34) & SearchString & Chr(34) & vbCrLf _
        & "was not found", vbCritical + vbOKOnly
    Else
        For Each Ndx In Array("B", "A", "H", "I", "J", "K")
            RSet temp = WS.Cells(1, Ndx).Value
            Default = Default & IIf(Default <> "", vbTab, "") & temp
        Next Ndx
            Default = Default & vbCrLf & WorksheetFunction.Rept("¯", Len(Default) + 5 * Ndx) & vbCrLf
            Report = Default & Report
        MsgBox Report
    End If
End Sub

assumes only one listing sheet.