Using Dynamic Named Range in VBA

[karraj]

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.

 

[Al Chara]

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

 

[Mark O'Brien]

Try:

<pre>
Sheets("Sheet1").Names("mynamedrange")...etc</pre>

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

 

[karraj]

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?

 

[NateO]

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.,

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

 

[karraj]

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