change 1000 cell names in VBA

[Superstition]

Hello, ive got 1000+ cells with names that all start with CCL (ie, CCL.EBITDA.2007, CCL.EBITDA.2008, CCL.EBITDA.2009, etc)

i would like to replace CCL with RBA using VBA.

ideas?

 

[AliGW]

Why not use find and replace?

 

[Norie]

Are these the only named cells in the workbook/on a specific worksheet?

 

[Superstition]

I need to clarify - these are the names of the cells, not names within the cells.

 

[Rick Rothstein]

Hello, ive got 1000+ cells with names that all start with CCL (ie, CCL.EBITDA.2007, CCL.EBITDA.2008, CCL.EBITDA.2009, etc)

i would like to replace CCL with RBA using VBA.

ideas?

Assuming the CCL is always upper case...

Sub ChangeDefinedNamesCCL()
  Dim N As Name
  For Each N In ThisWorkbook.Names
    If Left(N.Name, 3) = "CCL" Then N.Name = "RBA" & Mid(N.Name, 4)
  Next
End Sub

 

[Superstition]

perfect, thank you very much!