Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Using Dynamic Named Range in VBA

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,
    I have a dynamic named range in excel named sourcedata ( created using insert->name->define). I want to use it in VBA, i tried range("sourcedata").value , I am having problems,
    what is the syntax for using it in VBA
    Karraj.

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Make sure you are spelling it right, but it looks like the right syntax to me. What problems are you having?

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try:


    Sheets("Sheet1").Names("mynamedrange")...etc


    Put in whatever sheet you're using and the name you used for the range. That should work.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the replies,
    With the sheet name it is working well.

    In the spreadsheet, I am not able to view the dynamic ranges names in the dropdown menu in the toolbars, is there a way I can make it visible?

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can't see the name in the drop-down to the left of the formula bar? Odd.

    Also, you can use the name of the range itself to identify the sheet, e.g.,

    Code:
    Sheets(Range("test").Worksheet.Name).Range("test").Value

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am not able to see ONLY the dynamic named ranges. The other named ranges can be seen in the drop down menu to the left of formula bar!
    Am I missing something?
    Karraj

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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