Macro help: replacing named ranges names

[rcommer]

Hi,

I have 50+ named ranges in a file. All ranges have names begin with the same three characters "KLM_XXXXX". How can i replace the first three characters "KLM" with "TIY", so all names in the file become "TIY_XXXXX"?

Thanks,
Commer

 

[Col Delane]

I have 50+ named ranges in a file. All ranges have names begin with the same three characters "KLM_XXXXX". How can i replace the first three characters "KLM" with "TIY", so all names in the file become "TIY_XXXXX"?

Is this a one-off task or a regular thing?
If a one-off job, confirm that ALL target names - and no others - begin with "KLM".

I have some existing code that can be adapted to do this - it just needs answers to the above questions and some tweaking.

Cheers

 

[rcommer]

Hi Col,

It's a regular task, while i am creating TIY_ names this time, I may be creating other names like RHJ_ next times. but if you have a macro that can make one-off changes, i dont mind modify it each time...

C

 

[Col Delane]

I wrote several procedures to do stuff with Defined Names (e.g. list all in a new sheet with details of various properties, search for errors, search for unused names, etc.) - and then re-discovered the Name Manager add-in by Jan Karel Pieterse which does all that and more, and better than my code. However, my code is still useful for cycling through all names and doing stuff that meet whatever criteria one wants to build into it - and gives the user options to do stuff individually or in bulk.

One other question: Do all your Names that may require renaming have Workbook scope, or do you create some with Sheet scope?

 

[Col Delane]

Hi Commer

Here's my code - I suspect it's a little more elaborate than you expected. As I said earlier, I have various sub's that do stuff with names, and this is now just another one using the same base code. Unfortunately, it seems to behave a bit oddly at times (e.g. initially identifies names that meet the search criteria, but then on the second pass to make the change skips a name or two??), and I can't work out why. (The problem could be because of the names in my workbook/s rather than the code, but I don't really know one way or the other.)

I also had trouble getting a If Then, ElseIf, Else construct to work, so reverted to three separate If Then constructs.

If you work out how to fix the above issues, please post back with the solutions.

Anyway, test it out and see how you get on. (Be sure to save your file before running it so that you can abandon the changes if they are not what you're expecting.)

Sub BulkRenameNames()

' Date:     14/02/2014
' Action:   Added
' Author:   Colin Delane, CA, Financial Analyst/Modeller, Perth, Western Australia
' Purpose:  Renames all Defined Names matching the criterin
'----------------------------------------------------------------------------------------------------------------------------------

    'Procedure Scope Dimension Variables
    Dim lngC1 As Long
    Dim lngC2 As Long
    Dim lngC3 As Long
    
    Dim wbk As Workbook
    Dim oldNm As String
    Dim newNm As String
    Dim strSht As String    'Sheet name for local names
    
    Dim strNm1 As String    ' Search string
    Dim strNm2 As String    ' Replace string

    Dim strMsg1 As String
    Dim intButtons1 As Integer
    Dim strTitle1 As String
    Dim Response1

    Dim strMsg2 As String
    Dim intButtons2 As Integer
    Dim strTitle2 As String
    Dim Response2

    lngC1 = 0
    lngC2 = 0
    Set wbk = ActiveWorkbook
    
    On Error Resume Next
    
    ' Get search string
    Do Until strNm1 <> ""
        strNm1 = Application.InputBox(Prompt:="Enter the text string (Case sensitive!) to search for in the Defined Names in this workbook", Title:="Search String", Type:=2)
    Loop
    
    
    For lngC1 = 1 To wbk.Names.Count
        
        ' Get name without sheet name for those with Sheet/local scope
        oldNm = Right(Names(lngC1).Name, Len(Names(lngC1).Name) - InStr(1, Names(lngC1).Name, "!"))
        
        ' Count names in target workbook that contain search string
        If InStr(1, oldNm, strNm1) > 0 Then lngC2 = lngC2 + 1
        
    Next lngC1
    
    'Block IF #1
    If lngC2 = 0 Then
        MsgBox "No names in this workbook contain the search string!"
        Exit Sub
    End If  'Block IF #1
    
    ' Get replacement string
    Do Until strNm2 <> ""
        strNm2 = Application.InputBox(Prompt:="Enter the text string to replace [ " & strNm1 & " ] in the Defined Names in this workbook", Title:="Replacement String", Type:=2)
    Loop
    
    ' Count all names
    lngC3 = wbk.Names.Count
        
            strMsg1 = lngC2 & " names out of " & lngC3 & " names in this workbook contain the search string " & Chr(34) & strNm1 & Chr(34)
            strMsg1 = strMsg1 & vbCr & vbCr & "Click Yes to review each target name individually and choose whether or not to rename it."
            strMsg1 = strMsg1 & vbCr & vbCr & "Click No to perform a bulk re-naming of all matching names."
            strMsg1 = strMsg1 & vbCr & vbCr & "Otherwise click Cancel to stop and exit."
            intButtons1 = vbYesNoCancel + vbQuestion
            strTitle1 = "Review target names prior to renaming?"

        Response1 = MsgBox(strMsg1, intButtons1, strTitle1)

        'Block IF #2
        If Response1 = vbYes Then   ' User wants to review each name individually

            ' Review individual names if user chooses Yes
            ' Reset counters
            lngC1 = 0
            lngC2 = 0
            
            For lngC1 = 1 To wbk.Names.Count
            
                oldNm = Names(lngC1).Name
                
                ' Get name of sheet for those names with Sheet/local scope
                If Not (IsError(InStr(1, oldNm, "!"))) Then strSht = Left(oldNm, InStr(1, oldNm, "!"))


                oldNm = Right(Names(lngC1).Name, Len(Names(lngC1).Name) - InStr(1, Names(lngC1).Name, "!"))
                                
                
                ' Block IF #3
                If InStr(1, oldNm, strNm1) > 0 Then
                    
                    newNm = Replace(oldNm, strNm1, strNm2, 1)
                    
                        strMsg2 = ""
                        strMsg2 = "The name " & strSht & oldNm & " includes the search string " & Chr(34) & strNm1 & Chr(34)
                        strMsg2 = strMsg2 & vbCr & vbCr & "Click Yes to rename this name to " & Chr(34) & strSht & newNm & Chr(34)
                        strMsg2 = strMsg2 & vbCr & vbCr & "Click No to leave it as is."
                        strMsg2 = strMsg2 & vbCr & vbCr & "Otherwise click Cancel to stop."
                        intButtons2 = vbYesNoCancel + vbQuestion
                        strTitle2 = "Rename name?"
        
                    Response2 = MsgBox(strMsg2, intButtons2, strTitle2)
                
                    ' Replace strNm1 with strNm2
                    'Block IF #4
                    If Response2 = vbYes Then
                        
                        Names(lngC1).Name = newNm
                        lngC2 = lngC2 + 1
                    
                    ElseIf Response2 = vbCancel Then Exit For
                    End If  'Block IF #4
                End If  'Block IF #3
                
            Next lngC1
            
            MsgBox lngC2 & " names renamed."
            
        End If 'Block IF #2
        
        'Block IF #5
         If Response1 = vbNo Then   ' Bulk replacement

            ' Reset counters
             lngC1 = 0
             lngC2 = 0

            For lngC1 = 1 To wbk.Names.Count

                If Not (IsError(InStr(1, Names(lngC1).Name, "!"))) Then strSht = Left(Names(lngC1).Name, InStr(1, Names(lngC1).Name, "!"))
                
                oldNm = Right(Names(lngC1).Name, Len(Names(lngC1).Name) - InStr(1, Names(lngC1).Name, "!"))


                ' Replace strNm1 with strNm2
                ' Block IF #6
                If InStr(1, oldNm, strNm1) > 0 Then
                    newNm = Replace(oldNm, strNm1, strNm2, 1)
                    Names(lngC1).Name = newNm
                    lngC2 = lngC2 + 1
                End If 'Block IF #6
            
            Next lngC1

                MsgBox lngC2 & " names renamed."
            End If 'Block IF #5
        
        'Block IF #7
        If Response1 = vbCancel Then    ' User aborts
            MsgBox "Process cancelled at user's request."
            Exit Sub
        
        End If 'Block IF #7
        
End Sub

Hope it solves your problem.

Cheers