Is there a way to increment the name of a cell?

J

Justin

Merry Christmas everyone!!!

Was wondering if any of you can help me with this. I have
a financial model that currently has numerous named cells
in the format of XXX_03 ( the 03 is to denote 2003 ) but I
am making 2004 projection and therefore am wondering if
there was an easy way to sort of copy and paste the names
into a new column and automically renaming cells by an
increment of 1 to XXX_04. If anyone can help, it will be
greatly appreciated!

Thank you

Justin
 
B

Bob Phillips

see previous post

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
G

Guest

Hi Don

Thanks for the reply but the method you suggested wiull
only replace the contents within the cell as opposed to
the actual cell name. I not only need to change all cell
content from XXX_03 to XXX_04 but also the name of the
cell from XXX_03 to XXX_04

Justin
 
D

DoubleDip

I think you can do this best using code. In a new module, do something
like the following. Press F5 from within the code to run it. I could
have put some error handling in it but this should be a start.

Sub UpdateCellNames()
shtsource = "sheet1" 'or whatever your sheet name is
Sheets(shtsource).Select

Dim nCell As Name
Dim mypos
Dim SearchString As String
SearchString = "2003"

For Each nCell In Names
mypos = InStr(nCell.Name, SearchString)
If mypos > 0 Then
nCell.Name = Replace(nCell.Name, "2003", "2004")
End If
Next

End Sub
 
Top