+ Reply to Thread
Results 1 to 10 of 10

Fixing code for search engine for whole workbook

  1. #1
    Registered User
    Join Date
    05-24-2012
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Fixing code for search engine for whole workbook

    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!

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Fixing code for search engine for whole workbook

    Why split the database into multiple sheets?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    05-24-2012
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Fixing code for search engine for whole workbook

    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!

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Fixing code for search engine for whole workbook

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

  5. #5
    Registered User
    Join Date
    05-24-2012
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Fixing code for search engine for whole workbook

    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.

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Fixing code for search engine for whole workbook

    This this work for you?
    Please Login or Register  to view this content.
    Ben Van Johnson

  7. #7
    Registered User
    Join Date
    05-24-2012
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Fixing code for search engine for whole workbook

    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?

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Fixing code for search engine for whole workbook

    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?

  9. #9
    Registered User
    Join Date
    05-24-2012
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Fixing code for search engine for whole workbook

    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?

  10. #10
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Fixing code for search engine for whole workbook

    an alternative:
    Please Login or Register  to view this content.
    assumes only one listing sheet.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1