Defined Names

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" <rickv.1tbiqd_1123290320.3013@excelforum-nospam.com> wrote in
message news:rickv.1tbiqd_1123290320.3013@excelforum-nospam.com...
>
> 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" <normanjones@whereforartthou.com> wrote in message
news:%23TQYRTkmFHA.2916@TK2MSFTNGP14.phx.gbl...
> 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" <rickv.1tbiqd_1123290320.3013@excelforum-nospam.com> wrote in
> message news:rickv.1tbiqd_1123290320.3013@excelforum-nospam.com...
>>
>> 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" <normanjones@whereforartthou.com> wrote in message
news:eqrVGrkmFHA.2860@TK2MSFTNGP15.phx.gbl...
> 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" <normanjones@whereforartthou.com> wrote in message
> news:%23TQYRTkmFHA.2916@TK2MSFTNGP14.phx.gbl...
>> 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" <rickv.1tbiqd_1123290320.3013@excelforum-nospam.com> wrote in
>> message news:rickv.1tbiqd_1123290320.3013@excelforum-nospam.com...
>>>
>>> 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.