Check a list for whether a named range existsQuestion

[julhs]

I have a list of names that is “Named RangeA”
How can I check that the names in “Named RangeA” have Named Ranges, if not create it
“Named RangeA” list is:
Name1
Name2
Name3
Etc etc

 

[Jeffrey Mahoney]

You could use a function like this to check if a named range exists

Function CheckNamedRangeExists(NRTxt As String) As Boolean
  Dim NR As Name
  Dim NRName As String
  Dim E As Long
  Dim NRMatch As String
  
  NRMatch = UCase(NRTxt)
  For Each NR In ActiveWorkbook.Names
    NRName = UCase(NR.Name)
    E = InStr(NRName, "!")
    If E > 0 Then NRName = Mid(NRName, E + 1, 100)
    If NRName = NRMatch Then
      CheckNamedRangeExists = True
      Exit For
    End If
  Next NR
  
End Function

 

[julhs]

Thank you Jeffery
I’ve been working on this problem!!
I have managed to get to the point of creating a Named Range for ANY name in “Named RangeA” list that DOESN’T have a named range created.
BUT the “Refers To” range will need to be edited.
With my sub below is there a way to call the Name Manger and edit specifically the NEWLY added Named Range.
Yes I can call Name Manger with “Application.Dialogs(xlDialogNameManager).Show “ but I may not know what NEW named range has been added

Sub IfNoNamedRangeCreatOn ()
Dim n As Range
Dim NameExist As Boolean
Dim rngName
For Each n In Range("Named RangeA ")
rngName = n
NameExist = Evaluate("IsRef(" & rngName & ")")
Debug.Print NameExist
If NameExist = False Then
ActiveCell.Name = (n.Value)
'Now need to go and edit the “Refers To” range of the added NameRange
End If
Next
End Sub

 

[Jeffrey Mahoney]

I have managed to get to the point of creating a Named Range for ANY name in “Named RangeA” list that DOESN’T have a named range created.

I'm a little confused. What are you referring to with "Named RangeA". Is that a list you created outside the actual named ranges? Actual Named Ranges cannot have a space in them.

With my sub below is there a way to call the Name Manger and edit specifically the NEWLY added Named Range.

Are you manually adding new named ranges? Do you have a different macro doing that? Are other users creating these? Maybe you need to store a current list of named ranges in a special spot and then compare that list with the system named ranges; any system named ranges not on the saved list are new. I don't think Excel saves a Date/Time for each named range.

 

[Jeffrey Mahoney]

I don't know if you can use any of this code. It returns the names of all named ranges in the selected cells.

Sub CheckForNamedRange()
  Dim Nm As String
  Dim Rng As Range
  Dim NR As Name
  Dim NamRng As Range
  Dim A As String
  Dim ShtStr As String
  Dim X As Long
  Dim RngStr As String
  Dim Sht As Worksheet
  Dim Msg As String
  Dim Cel As Range
  Dim NmList As String
  Dim xArea As Range
  
  Set Rng = Selection
  Set Sht = ActiveSheet
  
  On Error Resume Next
  
  For Each NR In ActiveWorkbook.Names
    For Each xArea In Rng.Areas
      For Each Cel In xArea
        If InStr(NmList, Cel.ListObject.Name) = 0 Then
          Msg = Cel.ListObject.Name      'Table Name first
          NmList = NmList & "," & Cel.ListObject.Name
        End If
        
        A = ""
        A = NR.RefersToRange.Address(False, False)
        If A <> "" Then
          If InStr(NR.Name, "!") = 0 Then
            If InStr(NR.RefersTo, Sht.Name) > 0 Then
              Set NamRng = Range(A)
              If Not Intersect(NamRng, Cel) Is Nothing Then
                If InStr(NmList, NR.Name) = 0 Then
                  NmList = NmList & "," & NR.Name
                  If Msg <> "" Then
                    Msg = Msg & vbCrLf & NR.Name
                  Else
                    Msg = NR.Name
                  End If
                End If
              End If
            End If
          End If
        End If
      Next Cel
    Next xArea
  Next NR
  If Msg <> "" Then
    MsgBox Msg, vbOKOnly, "Selection belongs to These Named Ranges"
  Else
    MsgBox "None", vbOKOnly, "Selection belongs to These Named Ranges"
  End If
  
  On Error GoTo 0
  
End Sub

 

[julhs]

Re; “Named RangeA”
It is an actual named range, in compiling the initial question I inadvertantly added a space
Its going to take a while for me to go over and test that you have given me.
Bear with me, I'll come back to you asap

 

[julhs]

To answer your questions in post #4
Are you manually adding new named ranges, No
Do you have a different macro doing that, No
Are other users creating these, No

My code below does do what I set out to achieve, but ideally would like add a further refinement.
Code checks the names in "NamedRangeA" (a list of names on ActiveSheet that should have Named ranges for). If NO named range exists for the name, it creates it.
BUT BUT the "Refers To" reference of the newly created Named range will HAVE to be changed to the ACTUAL range reference that the name is MEANT to refer to (not the range that will have been assigned).
Ideally I would like to go directly to the Name Manger and the newly created name would be selected and in edit mode??!!
But best I’ve come up with is;

n.Select
Application.Dialogs(xlDialogNameManager).Show

That will at least select the name in the list that’s just had named range created and open the NameManager

Sub IfNoNamedRangeCreatOne ()
Dim n As Range
Dim NameExist As Boolean
Dim rngName

For Each n In Range("Named RangeA")
rngName = n
NameExist = Evaluate("IsRef(" & rngName & ")") 'returns True or False
Debug.Print NameExist 'only needed for testing purposes
If NameExist = False Then
ActiveCell.Name = (n.Value) 'new named range is created
n.Select
MsgBox "You have created a new named range;" & vbCrLf _
& " but need to edit the RefersTo range", vbOKOnly
Application.Dialogs(xlDialogNameManager).Show
End If
Next
End Sub