asked on

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

celazkon

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

ASKER CERTIFIED SOLUTION

celazkon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

pritamdutt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rob Brockett

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AndresHernando

ASKER

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

AndresHernando

ASKER

On my machine, I had to add ".Name" after "nm" (i.e., nm.Name) for the code to work.

AndresHernando

ASKER

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!! :-)