There are thousands of Defined name to be renamed. The prefix of thes
defined names is bb and would like to change the prefix to xxxbb. Thes
defined names are used in formulae. How do I create the macro or vba t
do the job.
Thanks for your help

--
rick
-----------------------------------------------------------------------
rickv's Profile: http://www.excelforum.com/member.php...fo&userid=2594
View this thread: http://www.excelforum.com/showthread.php?threadid=39350

Hi RickV,

Try:

'===========================
Public Sub Tester()
Dim Nme As Name
Dim sStr As String
Dim strOldName As String
Dim strNewName As String

Const myPrefix As String = "xxx"

For Each Nme In ActiveWorkbook.Names

If Nme.Name Like "bb*" Then
sStr = Nme.RefersTo
strOldName = Nme.Name
strNewName = myPrefix & strOldName
ActiveWorkbook.Names.Add strNewName, RefersTo:=sStr
Nme.Delete
End If

Next Nme

End Sub
'<<=======================


---
Regards,
Norman



"rickv" wrote in
message ...

There are thousands of Defined name to be renamed. The prefix of these
defined names is bb and would like to change the prefix to xxxbb. These
defined names are used in formulae. How do I create the macro or vba to
do the job.
Thanks for your help.


--
rickv
------------------------------------------------------------------------
rickv's Profile:
http://www.excelforum.com/member.php...o&userid=25942
View this thread: http://www.excelforum.com/showthread...hreadid=393501

Hi RickV,

And if these thousands of old defined names are already in use, you can run
the following sub to correct the formulae and implement the new defined
names.


'===========================
Public Sub Tester02()

Dim SH As Worksheet
Dim Rng As Range
Const strOld As String = "bb"
Const strNew As String = "xxxbb"


For Each SH In ActiveWorkbook.Worksheets
SH.Cells.Replace What:=strOld, _
Replacement:=strNew, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Next SH

End Sub


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi RickV,

Try:

'===========================
Public Sub Tester()
Dim Nme As Name
Dim sStr As String
Dim strOldName As String
Dim strNewName As String

Const myPrefix As String = "xxx"

For Each Nme In ActiveWorkbook.Names

If Nme.Name Like "bb*" Then
sStr = Nme.RefersTo
strOldName = Nme.Name
strNewName = myPrefix & strOldName
ActiveWorkbook.Names.Add strNewName, RefersTo:=sStr
Nme.Delete
End If

Next Nme

End Sub
'<<=======================


---
Regards,
Norman



"rickv" wrote in
message ...

There are thousands of Defined name to be renamed. The prefix of these
defined names is bb and would like to change the prefix to xxxbb. These
defined names are used in formulae. How do I create the macro or vba to
do the job.
Thanks for your help.


--
rickv
------------------------------------------------------------------------
rickv's Profile:
http://www.excelforum.com/member.php...o&userid=25942
View this thread:
http://www.excelforum.com/showthread...hreadid=393501

Hi RickV,

And if the workbook potentially contained strings comprising the 'bb'
substring, then you might wish to limit the replacement to formulae, e,g,:

'===========================
Public Sub Tester02A()

Dim SH As Worksheet
Dim Rng As Range
Const strOld As String = "bb"
Const strNew As String = "xxxbb"


For Each SH In ActiveWorkbook.Worksheets
Set Rng = Nothing
On Error Resume Next
Set Rng = SH.Cells.SpecialCells(xlFormulas)
On Error GoTo 0

If Not Rng Is Nothing Then
Rng.Replace What:=strOld, _
Replacement:=strNew, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End If
Next SH

End Sub
'<<===========================

Depending on the workbook's specific data, you may need futher to restrict
the ambit of the Rng variable and, perhaps, limit operation to specified
worksheets.


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi RickV,

And if these thousands of old defined names are already in use, you can
run the following sub to correct the formulae and implement the new
defined names.


'===========================
Public Sub Tester02()

Dim SH As Worksheet
Dim Rng As Range
Const strOld As String = "bb"
Const strNew As String = "xxxbb"


For Each SH In ActiveWorkbook.Worksheets
SH.Cells.Replace What:=strOld, _
Replacement:=strNew, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Next SH

End Sub


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi RickV,

Try:

'===========================
Public Sub Tester()
Dim Nme As Name
Dim sStr As String
Dim strOldName As String
Dim strNewName As String

Const myPrefix As String = "xxx"

For Each Nme In ActiveWorkbook.Names

If Nme.Name Like "bb*" Then
sStr = Nme.RefersTo
strOldName = Nme.Name
strNewName = myPrefix & strOldName
ActiveWorkbook.Names.Add strNewName, RefersTo:=sStr
Nme.Delete
End If

Next Nme

End Sub
'<<=======================


---
Regards,
Norman



"rickv" wrote in
message ...

There are thousands of Defined name to be renamed. The prefix of these
defined names is bb and would like to change the prefix to xxxbb. These
defined names are used in formulae. How do I create the macro or vba to
do the job.
Thanks for your help.


--
rickv
------------------------------------------------------------------------
rickv's Profile:
http://www.excelforum.com/member.php...o&userid=25942
View this thread:
http://www.excelforum.com/showthread...hreadid=393501

Thanks Norman... i will follow your instructions

--
rick
-----------------------------------------------------------------------
rickv's Profile: http://www.excelforum.com/member.php...fo&userid=2594
View this thread: http://www.excelforum.com/showthread.php?threadid=39350