Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing to see if a specific name has been already assigned to cel
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing to see if a specific name has been already assigned to cel
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing to see if a specific name has been already assigned tocel
Thank you...
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing to see if a specific name has been already assigned tocel
Thank you ... WindsurferLA
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access to a series, when it is assigned to an empty range | Excel Worksheet Functions | |||
TESTING A RANGE OF CELLS | New Users to Excel | |||
Testing a person's age to be within a range | Excel Worksheet Functions | |||
Assigned specific decimal space | Excel Programming | |||
Testing for content in Range | Excel Programming |