1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Macro using Find

Discussion in 'Business Applications' started by gundr1kr, Jul 14, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. gundr1kr

    gundr1kr Thread Starter

    Joined:
    Jun 26, 2006
    Messages:
    19
    In an excel macro, i was wondering if this was possible:

    You have a name in one of your worksheets. It is NOT separated by a comma. Example: "JOANNE PEREZ".

    I want the macro to take that name (above) and look in another one of the excel sheets and see if it is in there. However, in the other sheet- the names are formatted as last name, first name. Example: "PEREZ, JOANNE".

    Is it possible to have the macro return the name(either formatting) if found??? :eek:
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Yes no problem as long as the first and last names are spelt the same.
    They should both be in the same case as well, i.e. all upper case, all lower case or both capitalized names.
    If you attach an example of your database we can soon write some code for you.
     
  3. gundr1kr

    gundr1kr Thread Starter

    Joined:
    Jun 26, 2006
    Messages:
    19
    As_of_date Officer_nbr Officer_name
    06/30/2006 12:00:00 AM 02052 GRACE LI
    06/30/2006 12:00:00 AM 02212 J F RUMLER
    06/30/2006 12:00:00 AM 10418 PAUL A GRALEWSKI


    This information is located on a sheet called: Q Results - beofre lender list

    An example of the sheet in the workbook is called (All Users) that it would be looking under is:

    NAME ID HIRE_DATE WK_ST
    JACKSON, MIKE 999 XX/XX/XX MI
    LI, GRACE 111 XX/XX/XX MI
    LU, LUCY 444 XX/XX/XX MI
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    So which workbook is the search workbook and which is the one to be searched?
    How will you find or enter the name that is stored in the search workbook to go and look for it?
    will you use Excel find and then run the macro?
    Is the data that you have shown all in one cell or in seperate cells?
    When you find the name in the second Workbook what do you want to do with it?
    To write the code we ned to know which column(s) the name(s) are held in on the respective workbooks.
     
  5. gundr1kr

    gundr1kr Thread Starter

    Joined:
    Jun 26, 2006
    Messages:
    19
    The second example of data(All Users) is the sheet that is the one to be searched.

    I was hoping the macro would use a find and if then statement: if it finds it, it returns the name. If it doesnt find it, it will return: "Not in All Users"

    The data that i wrote are in seperate cells as follows:
    A B C
    1 As_of_date Officer_nbr Officer_name
    2 06/30/2006 12:00:00 AM 02052 GRACE LI
    3 06/30/2006 12:00:00 AM 02212 J F RUMLER
    4 06/30/2006 12:00:00 AM 10418 PAUL A GRALEWSKI

    A B C D
    1 NAME ID HIRE_DATE WK_ST
    2 JACKSON, MIKE 999 XX/XX/XX MI
    3 LI, GRACE 111 XX/XX/XX MI
    4 LU, LUCY 444 XX/XX/XX MI



    When the name is found, i want either it to say that the name is in the workbook, or that it is not found.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Ok, it will take a little while as I will have to reverse the first and last names and add the comma to the search string.
    How will you identify the name in the first workbook before running the macro to find it, do you want to just go to the cell that has the name in for now?
     
  7. gundr1kr

    gundr1kr Thread Starter

    Joined:
    Jun 26, 2006
    Messages:
    19
    Yes, I want to just go to the cell that has the name in for now
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Ok, here are my worksheets, the first is called "find name", this has your names in from your Q results sheet. It has a command button on which has the VBA that does the work.
    The second sheet is called "Filetosearch" and is the equivelent of your "All Users" sheet.
    This VBA assumes that both workbooks are already open.
    There is a minor problem with the data that you posted for the All Users names, if you go to a cell with a name in and look in the edit line you will find that the names have blank spaces on the end. The VBA takes care of this for you.
    The only name on both sheets is Grace Li.
    All you have to do is change the name of the second worksheet to "All Users.xls"
     

    Attached Files:

  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/483184

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice