Testing to see if a specific name has been already assigned to cellor range.

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.
>

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...

Thank you ... WindsurferLA