Sleeper: Change Cell's named reference via VBA

[DeenaT]

Is there a way to change the referenced name of a cell through vba?

Background:
I have a column where I have a bunch of named cells. I cannot figure out how to get the name of a specific cell. For example, is there a way to get the referenced name of cell B2 and then change one character and name cell C2?

[Dave]

Hi DeenaT. You didn't say what character to remove, so the last character is gone. HTH. Dave

Dim rng As Range, TempStr As String
Set rng = Sheets("sheet1").Range("B" & 2)
TempStr = rng.Name.RefersToRange.Name.Name
TempStr = Left(TempStr, Len(TempStr) - 1)
Sheets("sheet1").Names.Add Name:=TempStr, RefersTo:= _
Sheets("sheet1").Range("C" & 2)

[Paul_Hossler]

Is there a way to change the referenced name of a cell through vba?

Background:
I have a column where I have a bunch of named cells. I cannot figure out how to get the name of a specific cell. For example, is there a way to get the referenced name of cell B2 and then change one character and name cell C2?


Do you mean that B2 has a formula that referes to a named range (i.e. B2 = 100 * TaxRate) or that B2 is named TaxRate?

What to you mean by " get the referenced name of cell B2 and then change one character and name cell C2"

Examples would help

[JarodSimon]

Do you mean that B2 has a formula that referes to a named range (i.e. B2 = 100 * TaxRate) or that B2 is named TaxRate?

What to you mean by " get the referenced name of cell B2 and then change one character and name cell C2"

Examples would help

The examples are easy to understand and effective. Thank