Find Range Name Base on Range Address

[ninjamyst]

Hi,

This is a pretty simple problem that I can't figure out for the past 3 hours. How do I find the name of a range given the range's address? The big problem is that range.name does not give the name I gave the range.

Dim lRange as Excel.Range

myWorkbook.Name.Add "Range1" rangeAddress TRUE
lRange = myWorksheet.Range(rangeAddress)
MsgBox lRange.name

Note that myWorkbook has already been created and myWorksheet is activeworksheet of myWorkbook. I want to retrieve the string "Range1" given only the rangeAddress. Thanks!

 

[TonyJollans]

Hi ninjamyst,

The problem is that you're not setting the name correctly so you can't retrieve it. When you've set it, try to Go To in Excel (not in VBA) and you should see what I mean.

Providing rangeaddress is absolute, something like this should be better:

[blue]myWorkbook.Names.Add "Range1", "=" & myworksheet.name & "!" & rangeAddress, TRUE[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[