+ Reply to Thread
Results 1 to 8 of 8

Macro to define name range based on user input

  1. #1
    Registered User
    Join Date
    05-25-2007
    Posts
    8

    Macro to define name range based on user input

    Hi,

    I would like to have a macro that would define a name range dynamically. It will be based on what the user input into a cell. Below is an example:


    SHEET1
    A B C
    1 BOB
    2
    3

    SHEET2
    A B C
    1 JOH1 John McDonalds
    2 JOH2 John Lee
    3 BOB1 Bob Jackson
    4 BOB2 Bob Clark


    So in the example, when a user type into worksheet "SHEET1" and cell "A1" the word "BOB", the macro would look for all cells in "SHEET2" column "A" that contain the word BOB and then define a name range "myNAMES" to cells "C3:C4".

    Can someone please help me with this?

    Thanks,
    truongn2

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Truongn2,

    This macro starts searching Sheet2 at cell A1 to the last entry in column A for any cell matching the contents of cell A1 on Sheet1. A named range is created using column C's addresses. The search is not case sensitive.
    Please Login or Register  to view this content.
    Installing the Macro:
    1. Copy the Macro code above using CTRL+C
    2. Open your Workbook and Right Click the Tab of Sheet1
    3. Click View Code
    4. Use CTRL+V to Paste the macro into the workbook
    5. Save the changes using CTRL+S
    6. Exit the Visual Basic Editor using ALT+q

    Sincerely,
    Leith Ross

  3. #3
    Registered User
    Join Date
    05-25-2007
    Posts
    8
    Thank you for helping me.

    However, after adding the code, I try entering the word "BOB" into cell A1 of SHEET1, it give me and error "The name is not valid" and when I hit debug, it takes me to this part of your code and highlight it in yellow:

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    05-25-2007
    Posts
    8
    I open a new workbook and tried out your code. And I think I have found two things that might be causing the problem.

    1. The name range that is created is named after what the user typed in, like "BOB" or "JOH", instead of a static one "myNAMES".

    2. The name range created has the wrong syntax in the "Refer to" box. It is ="Sheet2!$C$1:$C$2". For some reason, the macro is adding the quotation marks into the name range, causing for it to not work. It suppose to be =Sheet2!$C$1:$C$2 (without the "")

  5. #5
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,220
    This should do what you want. If the name in sheet1A1 is not in sheet2colA, then Range("myNames") is not changed.

    The syntax used avoids having to use the .Add method for Names.

    The code goes in the Sheet code module for sheet 1.

    Please Login or Register  to view this content.
    Last edited by mikerickson; 08-02-2007 at 08:25 PM.

  6. #6
    Registered User
    Join Date
    05-25-2007
    Posts
    8
    Hi,

    I am sorry to keep bothering you, but I am still having trouble with this code. When I type something into cell A1 of Sheet1, it is giving me an error "Metho 'Range' of object '_Worksheet' failed"

    I have attached my excel example in this post. I am using Excel 2007 by the way. If the Excel version is the problem, then I can also use Excel 2003.

    Thank you for helping
    truongn2
    Attached Files Attached Files

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Truongn2,

    This is an interesting problem. I dug deeper into what the code was doing, and why it wasn't working correctly. What I found out was surprising.

    When you create a named range using the Excel menu, the range is global in scope. You simply use the name to access the range the name defines. When a named range is created using VBA, the range is local in scope. That is, to access the range you must either have the worksheet the range is on activated, or when using code, prefix the range name with the worksheet name. From a programming view point, this defeats the purpose of a named range since it acts like any other locally declared variable.

    I can't tell you anything about Excel 2007, since I don't have it. I do know it is very different from all previous versions, and fraught with problems of it own. Whether named ranges in 2007 behave differently, is question some else will have to answer. Currently, I have not been able to find a work around to correct this problem.

    Sincerely,
    Leith Ross

  8. #8
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,220
    Perhaps if the routine were moved to a normal module and called by the Change event.
    in the Sheet1 code module
    Please Login or Register  to view this content.
    in a normal module
    Please Login or Register  to view this content.

    There's an interesting suggestion at the cross post on MrExcel.
    Last edited by mikerickson; 08-03-2007 at 09:04 PM.

+ 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