Solved: Test if there is a Ranged Name already

[Djblois]

I use a Named Range for pivot tables. However I want to be able to test if the range exists already before I create it. I have not been able to figure this out and I have tried for a while.

[RonMcK]

Daniel,

In what context are you attempting to test whether a named range already exists in your workbook?

Insert > Name > Paste (or Define or Apply) pops up a list of the existing named ranges. Is this what you're looking for?

Do you want to perform the text from within our VBA code? If so, what do you want the code to do if the test fails (range exists)?

Thanks,

[Zack Barresse]

Hi there Daniel,

http://vbaexpress.com/kb/getarticle.php?kb_id=729

:)

[RonMcK]

Drat, I need to check the KBase before I type. :doh:

Thanks, Zach.

[Zack Barresse]

LOL! I knew right where that one was.. I know the author.. ;)

[RonMcK]

So, I noticed when I clicked on the KBase link. I suspect you have this and some others on quick reference list of your favorite Top 40 hits of all time. :thumb

[mdmackillop]

An alternative

Option Explicit
Option Compare Text
Sub FindNames()
Dim ToCheck As String, nm As Name
ToCheck = InputBox("Enter name to check")
For Each nm In ActiveWorkbook.Names
If nm.Name = ToCheck Then
MsgBox "Used"
Exit Sub
End If
Next
MsgBox "OK"
End Sub

[Zack Barresse]

That list of 40 are the entries I've personally submitted. I have some others but I haven't found time to get them in or perfect them.

As far as MD's solution, it works; opposed to mine which is a Function, his is a sub routine and on the activeworkbook only. :)

[mdmackillop]

As far as MD's solution, it works; opposed to mine which is a Function, his is a sub routine and on the activeworkbook only
I didn't pick up from the OP's question that he wanted to find range names in other than the active workbook.
:devil2:

[Djblois]

Thank you all for your help. It is working exactly as needed.