Testing for named cells (i.e., ranges)

The following code is written to test for the existence of a cell named
"anchor" on the Active Sheet but it does not work. If the named cell exists,
that named range is supposed to become the active cell.

Something is wrong with the second statement. Even if a cell named "anchor"
exists on the active sheet, the resulting CellName is Nothing. Why?

Dim CellName as Name

On Error Resume Next
Set CellName = ActiveSheet.Names("anchor").Name
On Error GoTo 0
If Not CellName Is Nothing Then
Application.Goto Reference:=CellName
Else
Application.Goto Reference:=Range("A1")
End If

Thank you.

John Wirt

ActiveSheet.Names will only return names which are specific to that
Worksheet. ie. Sheet1!Anchor

Try this:

Sub test()
Dim strName As String, nam As Name

strName = "anchor"

On Error Resume Next
Set nam = ActiveSheet.Names(strName)
If nam Is Nothing Then Set nam = Names(strName)
If Not nam.RefersToRange.Worksheet Is ActiveSheet Then Set nam = Nothing
On Error GoTo 0
If Not nam Is Nothing Then
nam.RefersToRange.Select
Else
Range("A1").Select
End If
End Sub



--
Rob van Gelder - http://www.vangelder.co.nz/excel


"John Wirt" <[email protected]> wrote in message
news:[email protected]...
> The following code is written to test for the existence of a cell named
> "anchor" on the Active Sheet but it does not work. If the named cell
> exists,
> that named range is supposed to become the active cell.
>
> Something is wrong with the second statement. Even if a cell named
> "anchor"
> exists on the active sheet, the resulting CellName is Nothing. Why?
>
> Dim CellName as Name
>
> On Error Resume Next
> Set CellName = ActiveSheet.Names("anchor").Name
> On Error GoTo 0
> If Not CellName Is Nothing Then
> Application.Goto Reference:=CellName
> Else
> Application.Goto Reference:=Range("A1")
> End If
>
> Thank you.
>
> John Wirt
>
>

John,
try this,

Sub DoIt()
On Error GoTo RngError
Range("anchor").Select
Exit Sub
RngError:
Application.Goto Reference:=Range("A1")
Exit Sub
End Sub

Regards
Neil

"John Wirt" <[email protected]> wrote in message
news:[email protected]...
> The following code is written to test for the existence of a cell named
> "anchor" on the Active Sheet but it does not work. If the named cell
> exists,
> that named range is supposed to become the active cell.
>
> Something is wrong with the second statement. Even if a cell named
> "anchor"
> exists on the active sheet, the resulting CellName is Nothing. Why?
>
> Dim CellName as Name
>
> On Error Resume Next
> Set CellName = ActiveSheet.Names("anchor").Name
> On Error GoTo 0
> If Not CellName Is Nothing Then
> Application.Goto Reference:=CellName
> Else
> Application.Goto Reference:=Range("A1")
> End If
>
> Thank you.
>
> John Wirt
>
>

In essence, you are trying to set an object variable to a string value,
which errors out, but you are not trapping it because of the OnError Next.

This works

Dim CellName As Range

On Error Resume Next
Set CellName = ActiveSheet.Range("anchor")
On Error GoTo 0
If Not CellName Is Nothing Then
Application.Goto Reference:=CellName
Else
Application.Goto Reference:=Range("A1")
End If

if you want to select the whole range, or

Dim CellName As Range

On Error Resume Next
Set CellName = ActiveSheet.Range("anchor")(1, 1)
On Error GoTo 0
If Not CellName Is Nothing Then
Application.Goto Reference:=CellName
Else
Application.Goto Reference:=Range("A1")
End If

if you just want to select the first cell in the range.

Be aware that workbook and worksheet level names can cause some strange
effects. If you want more info, see
http://www.xldynamic.com/source/xld.Names.html


--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Wirt" <[email protected]> wrote in message
news:[email protected]...
> The following code is written to test for the existence of a cell named
> "anchor" on the Active Sheet but it does not work. If the named cell
exists,
> that named range is supposed to become the active cell.
>
> Something is wrong with the second statement. Even if a cell named
"anchor"
> exists on the active sheet, the resulting CellName is Nothing. Why?
>
> Dim CellName as Name
>
> On Error Resume Next
> Set CellName = ActiveSheet.Names("anchor").Name
> On Error GoTo 0
> If Not CellName Is Nothing Then
> Application.Goto Reference:=CellName
> Else
> Application.Goto Reference:=Range("A1")
> End If
>
> Thank you.
>
> John Wirt
>
>