Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

Excel VBA - Change all range names that contain a specific txt string

How can I replace the text string "Reviewer" with "Approver" in all range names of a workbook?

For example, the range name "rFirstReviewerLastName"  would be changed to "rFirstApproverLastName"

Something like the code below --which I got from a prior post-- (except that I made up the CONTAINS function).

Sub M_ChangeRangeNamesReviewerToApprover()
    Dim nm
    For Each nm In ThisWorkbook.Names

   If CONTAINS("Reviewer",nm.Name) Then nm.Name = Replace(nm.Name, "Reviewer", "Approver")

    Next nm

End Sub


Thanks, --Andres
0
AndresHernando
Asked:
AndresHernando
3 Solutions
 
celazkonCommented:
I think your code is okay, it should work. The only thing missing is the end if
Also, the if statement is useless, you don't need to check the existence. Use only the replace function. If it doesn't finds the string to replace, then the script simply goes on.

Sub M_ChangeRangeNamesReviewerToApprover()
    Dim nm
    For Each nm In ThisWorkbook.Names
    Replace(nm.Name, "Reviewer", "Approver")
    Next nm
End Sub
0
 
celazkonCommented:
Sorry it shoul be like this:

Sub M_ChangeRangeNamesReviewerToApprover()
    Dim nm
    For Each nm In ThisWorkbook.Names
    Replace(nm, "Reviewer", "Approver")
    Next nm
End Sub
0
 
pritamduttCommented:
Here is the code to change name ranges

Sub M_ChangeRangeNamesReviewerToApprover()
    Dim nm
    Dim SearchFor, ReplaceWith
    SearchFor = "Reviewer"
    ReplaceWith = "Approver"
    
    For Each nm In ThisWorkbook.Names
    
        If InStr(1, nm.Name, SearchFor, vbTextCompare) > 0 Then nm.Name = Replace(nm.Name, "Reviewer", "Approver")
    
    Next nm

End Sub

Open in new window


hope this helps.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
broro183Commented:
hi,

It looks like you have been given solutions already so what follows is just an extra which may be useful in the future...

I suggest checking out the JKP Name Manager, which is a very useful and free excel addin, collaboratively created by Jan Karel Pieterse (JKP), Charles Williams, (www.decisionmodels.com) and Matthew Henson (mhenson@mac.com) and can be downloaded from: http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp?AllComments=True

I use it in my work almost every day and it is regularly updated as bugs or optimisations are identified so I occasionally compare the latest Build number on the website (currently it is #630) with the version on my machine.

Rob
0
 
AndresHernandoAuthor Commented:
Thank you both for your solutions!!!  Both worked.
celazkon is shorter so I will go with that one, but I had to add ".Name" to make it work (i.e., nm.Name), as pritamdutt had in his code.

NOTE:  some of my worksheet names (tabs) included the string to be replaced.  This caused much grief until I figured out what was happening and changed the names of those worksheets.

Both solutions added value, so I will split points 300 / 200.  I hope this is OK.

Thanks again!!!  --Andres
0
 
AndresHernandoAuthor Commented:
On my machine, I had to add ".Name" after "nm"  (i.e.,  nm.Name) for the code to work.
0
 
AndresHernandoAuthor Commented:
Excellent add-in referred by boro183.  Also added value, so I "stole" 50pts from celazkon and pritamdutt to give to boro.

THANK YOU ALL FOR YOUR INPUT .  VERY HELPFUL!!  :-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now