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.

Solved: Retrieve a cell/range name using VBA in Excel

Discussion in 'Business Applications' started by seleseped, Apr 29, 2013.

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

    seleseped Thread Starter

    Joined:
    Apr 15, 2013
    Messages:
    15
    I have several Excel 2010 workbooks that each have a cell of the same name, e.g., penicillin. I would like code that references that particular cell/range name no matter which workbook is open so that I can use the same code to perform a specific task. I'm thinking that the code would be something like:

    Dim WorkbookName
    WorkbookName = ThisWorkbook.Name

    If Range("WorkbookName!penicillin") = True Then Range("DrugIndex") = 1

    The code works if the actual name of the workbook is used (instead of WorkbookName) in the first half of the 'IF' statement so I think that my syntax is not correct when generically refering to the open workbook.

    Again, your expertise is much appreciated.
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Not sure how you got that to manually work using the workbook name since I thought the named range required the sheet name be included in front of the exclamation character. In general to make something like that work you would keep the variable outside of the quotation marks and then concatenate the two strings together using an ampersand like I've done below.


    Rollin
     
  3. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    hi seleseped

    I think you are over complicating this. Named ranges must be unique - meaning you can'd have a name "DrugIndex" in your current workbook and a name "DrugIndex" in your current workbook that refers to a range in another workbook.

    This means that the name you are referring to will always look for it in the current workbook. All you need is;

    If Range("penicillin") = True Then Range("DrugIndex") = 1
     
  4. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    @ Rollin_Again

    I couldn't get your solution to work. I believe this is because, as you said, a "Range" requires that you specify the Worksheet. I tried many combinations of using the workbook name in a Range statement and couldn't get any to work.

    But as I said I believe that using

    Code:
    If Range("penicillin") = True Then Range("DrugIndex") = 1
    will work because even if there are other workbooks open that contain the same Names this statement will only consider the currently active workbook.
     
  5. seleseped

    seleseped Thread Starter

    Joined:
    Apr 15, 2013
    Messages:
    15
    Thanks to the both of you. I got Rollin's suggestion [Range(WorkbookName & "!penicillin")] to work. Not sure how or why my original code worked but it was sheer beginner's luck! Again, thank you greatly for your help.
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    While this is true, it's not a good idea. You should (almost) never assume or work on the active book/sheet. This may not always be the case, and such will break/error code. :cool:
     
  7. 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!

Thread Status:
Not open for further replies.

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

  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