Use "Name" of named range in a formula?

[mickyh]

hello,
I'm trying to set up a spreadsheet where a specific named range will change depending on the value of a cell.
Thanks to SydneyGeek for the code below but I'm looking to further enhance this. In my NameList I'm generating the new name for the range(the range, in this case, will always be a single cell) in the right column using =Concatenate "newName"&B2 to provide me with the new name. this gives me the new name of the range plus the number in B2 (which will always be 1-30) I then want to dynamically change the "Oldname" on the left-hand side of the Namelist to the new name (so that the code will work next time there is a change also)
Any suggestions gratefully accepted and thanks in anticipation.

Sub NameChanger()
    Dim arNames()
    Dim nm As Name
    Dim i As Integer
    Dim sname As String
    sname = ActiveSheet.Name
    
    arNames = Sheets(sname).Range("NameList").Value
    For i = LBound(arNames) To UBound(arNames)
        For Each nm In ActiveWorkbook.Names
            If nm.Name = arNames(i, 1) Then
                nm.Name = arNames(i, 2)
            End If
        Next nm
    Next i
End Sub

 

[StephenCrump]

I'm not totally clear what you're asking, or why you might want to do this, but I am guessing maybe:

Sub NameChanger()
    
    Dim arNames As Variant
    Dim nm As Name
    Dim i As Long, j As Long
    
    arNames = Range("NameList").Value
    j = Range("B2").Value
    
    For i = 1 To UBound(arNames)
        On Error Resume Next
        Set nm = ThisWorkbook.Names(Range("NameList")(i, 1).Value)
        If Err.Number = 0 Then
            On Error GoTo 0
            nm.Name = arNames(i, 2)
            arNames(i, 1) = nm.Name
            arNames(i, 2) = arNames(i, 2) & j
        End If
    Next i
    
    Range("NameList").Value = arNames
    
End Sub

 

[mickyh]

I'm not totally clear what you're asking, or why you might want to do this, but I am guessing maybe:

Sub NameChanger()
   
    Dim arNames As Variant
    Dim nm As Name
    Dim i As Long, j As Long
   
    arNames = Range("NameList").Value
    j = Range("B2").Value
   
    For i = 1 To UBound(arNames)
        On Error Resume Next
        Set nm = ThisWorkbook.Names(Range("NameList")(i, 1).Value)
        If Err.Number = 0 Then
            On Error GoTo 0
            nm.Name = arNames(i, 2)
            arNames(i, 1) = nm.Name
            arNames(i, 2) = arNames(i, 2) & j
        End If
    Next i
   
    Range("NameList").Value = arNames
   
End Sub

Thank you for your reply, I appreciate my question wasn't very clear. I'm basically looking for a way to dynamically change named ranges which are used elsewhere on other sheets. Changing the name using SydneyGeek's code worked well but it used a two-column list, (existing names on the left and new names on the right) however my "new Names" are generated by formula elsewhere on the sheet so once the code had run the "Existing Names" list was no longer accurate and I wouldn't necessarily know what the new names were.
While the code you sent me didn't quite do what I was looking for it did get me thinking differently so I have found a solution (probably not a very elegant one in terms of coding but it does what I need!) so in a roundabout way, you have solved my problem.
Thank you very much.

Sub NameChanger()
    Dim arNames()
    Dim nm As Name
    Dim i As Integer
    Dim sname As String
    sname = ActiveSheet.Name
    
    arNames = Sheets(sname).Range("AA2:AB3").Value
    For i = LBound(arNames) To UBound(arNames)
        For Each nm In ActiveWorkbook.Names
            If nm.Name = arNames(i, 1) Then
                nm.Name = arNames(i, 2)
            End If
        Next nm
    Next i
    
    With Range("AB2:AB3").Select
    Selection.Copy
    End With
    With Range("AA2").Select
    Selection.PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False
    
    End With
    
End Sub

 

[StephenCrump]

'You can replace this:
With Range("AB2:AB3").Select
    Selection.Copy
    End With
    With Range("AA2").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End With

'With:
Range("AA2:AA3").Value = Range("AB2:AB3").Value

But it's not a good idea to hard-code ranges like "AA2:AA3" into VBA. If the range changes in Excel, e.g. because you insert/delete rows/columns, the VBA reference will be wrong and your code will fail.

Thanks for clarifying that the Concatenate was happening in Excel, not in VBA. My slightly modified code would be:

Sub NameChanger()
    
    Dim arNames As Variant
    Dim nm As Name
    Dim i As Long
    
    arNames = Range("NameList").Value
    
    For i = 1 To UBound(arNames)
        On Error Resume Next
        Set nm = ThisWorkbook.Names(Range("NameList")(i, 1).Value)
        If Err.Number = 0 Then
            On Error GoTo 0
            nm.Name = arNames(i, 2)
            arNames(i, 1) = nm.Name
        End If
    Next i
    
    Range("NameList").Value = arNames
    
End Sub

 

[mickyh]

'You can replace this:
With Range("AB2:AB3").Select
    Selection.Copy
    End With
    With Range("AA2").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End With

'With:
Range("AA2:AA3").Value = Range("AB2:AB3").Value

But it's not a good idea to hard-code ranges like "AA2:AA3" into VBA. If the range changes in Excel, e.g. because you insert/delete rows/columns, the VBA reference will be wrong and your code will fail.

Thanks for clarifying that the Concatenate was happening in Excel, not in VBA. My slightly modified code would be:

Sub NameChanger()
   
    Dim arNames As Variant
    Dim nm As Name
    Dim i As Long
   
    arNames = Range("NameList").Value
   
    For i = 1 To UBound(arNames)
        On Error Resume Next
        Set nm = ThisWorkbook.Names(Range("NameList")(i, 1).Value)
        If Err.Number = 0 Then
            On Error GoTo 0
            nm.Name = arNames(i, 2)
            arNames(i, 1) = nm.Name
        End If
    Next i
   
    Range("NameList").Value = arNames
   
End Sub

Thank you for that, I had tried to use the name but couldn't figure it out.
I was also trying to get this to run on the active sheet only (or on a specific sheet) but that doesn't seem to be possible, so I may have to think of another solution.
Thanks again for your help, I have just started working my way through an online VBA course but clearly have a long way to go!

 

[StephenCrump]

I had tried to use the name but couldn't figure it out.

In more general terms:

With Range("NameList")
    .Columns(1).Value = .Columns(2).Value
End With

I was also trying to get this to run on the active sheet only (or on a specific sheet) but that doesn't seem to be possible, so I may have to think of another solution.

I'm not sure what you mean? You can have code run on any sheet you like.

An unqualified reference like Range("A1") by default refers to the ActiveSheet (in the ActiveWorkbook). But you can specify both Workbook and Worksheet if you want, and in general terms it's better to be specific, rather than relying on the sheet you want to work on being the Activesheet at the time.

Obviously if you are using range names with Workbook scope, you don't need to qualify with a Worksheet name.

 

[StephenCrump]

I have just started working my way through an online VBA course but clearly have a long way to go!

You could do worse than loiter on this forum.

FYI, check out this amazing list of resources from forum member, hiker95: Big Shoutout!