I want a macro to be able to reassign the cell location associated with
a “name”. I believe to do so requires three steps:

(1) Test to see if name has already been assigned, and if so,
(2) DELETE.
(3) Assign the name to the current active cell.

PROBLEM AREA - It would seem to me that the test would use code such as:
If[ActiveWorkbook.Names("Priority").Value 0] then ….
BUT I KNOW THIS CODE IS NOT RIGHT.

If it is already in use, the name can be removed by the code:
ActiveWorkbook.Names("Priority").Delete

The name can then be added back using something like:
ActiveWorkbook.Names.Add Name:="TheData", RefersTo:="=Sheet1![A1,E10]"
Or alternatively
Range("A1:E10").Name = "Priority"
Or possibly
ActiveWorkbook.ActiveSheet.ActiveCell.Name = "Priority"

SUGGESTIONS WOULD BE APPRECIATED for the proper syntax to test for a
specific name having been already assigned to a cell or region.

You do not have to check if the name allready exists if you are just going to
delete it and re-use. By adding the name with VBA code it is automatically
changed to the new range if if allready exists. So you can just use something
like:

ActiveWorkbook.Names.Add Name:="NamedRange" _
, RefersToR1C1:="=Sheet3!R6C2:R13C5"

This will work whether or not the name already exists.

Hope this helps
Rowan

"windsurferLA" wrote:

I want a macro to be able to reassign the cell location associated with
a €œname€�. I believe to do so requires three steps:

(1) Test to see if name has already been assigned, and if so,
(2) DELETE.
(3) Assign the name to the current active cell.

PROBLEM AREA - It would seem to me that the test would use code such as:
If[ActiveWorkbook.Names("Priority").Value 0] then €¦.
BUT I KNOW THIS CODE IS NOT RIGHT.

If it is already in use, the name can be removed by the code:
ActiveWorkbook.Names("Priority").Delete

The name can then be added back using something like:
ActiveWorkbook.Names.Add Name:="TheData", RefersTo:="=Sheet1![A1,E10]"
Or alternatively
Range("A1:E10").Name = "Priority"
Or possibly
ActiveWorkbook.ActiveSheet.ActiveCell.Name = "Priority"

SUGGESTIONS WOULD BE APPRECIATED for the proper syntax to test for a
specific name having been already assigned to a cell or region.

Thank you...

No. If you use the Names collection, all you need to do is assign the name.
if it existed, it will be replaced.

Option Explicit
Const REF_NAME As String = "myTarget"
Sub RenameSelection()
Selection.Name = REF_NAME
End Sub

open a new workbook
add the code above to a standard module

select a cell on sheet1, run the code. go to the Insert/Names/Define menu &
check where MyRaget is referencing.
Go to sheet2 and select some cells. run the code then re-check the names list


"windsurferLA" wrote:

I want a macro to be able to reassign the cell location associated with
a €œname€�. I believe to do so requires three steps:

(1) Test to see if name has already been assigned, and if so,
(2) DELETE.
(3) Assign the name to the current active cell.

PROBLEM AREA - It would seem to me that the test would use code such as:
If[ActiveWorkbook.Names("Priority").Value 0] then €¦.
BUT I KNOW THIS CODE IS NOT RIGHT.

If it is already in use, the name can be removed by the code:
ActiveWorkbook.Names("Priority").Delete

The name can then be added back using something like:
ActiveWorkbook.Names.Add Name:="TheData", RefersTo:="=Sheet1![A1,E10]"
Or alternatively
Range("A1:E10").Name = "Priority"
Or possibly
ActiveWorkbook.ActiveSheet.ActiveCell.Name = "Priority"

SUGGESTIONS WOULD BE APPRECIATED for the proper syntax to test for a
specific name having been already assigned to a cell or region.

Thank you ... WindsurferLA