Adding and deleting Range Names

[Paul]

I tried to write code that when a certain value is found
in a cell, that cell gets a range name VALUE1 that I can
return to later via: Range("VALUE1").Select. The next time
such a cell is found, VALUE1 is deleted from the name list
and re-applied to the new cell. This would enable me to
dynamically mark certain locations.

As a test, I wrote this:

Names.Add Name:="XXAA", RefersTo:="=Sheet1!A1"
Range("XXAA").Select

This doesn't work. Line 2 crashes.

Any insights appreciated
Paul

 

[BrianB]

Your code works OK for me (XL97 NT4). Your code is a bit "lazy" in
omitting some Parent detail, but this should not be much of a problem
here.

1. I assume that you do, in fact, have a sheet named Sheet1.
2. To make a selection the sheet must be the active one.
3. Application.Goto is often better than Select (one line of code).
4. You may not need to Select the range anyway if you just want to
access its data. eg. MyValue =
Worksheets("Sheet1").Range("XXAA").Value

Try :-
ActiveWorkbook.Names.Add Name:="XXAA", RefersTo:="=Sheet1!A1"
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("XXAA").Select


Regards
BrianB
=======================================