Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for named cells (i.e., ranges)
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for named cells (i.e., ranges)
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for named cells (i.e., ranges)
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Testing selection against named range | Excel Discussion (Misc queries) | |||
Named Cells (Ranges) | Excel Discussion (Misc queries) | |||
Replace a spreadsheets named cells/ranges with exact cell address. | Excel Discussion (Misc queries) | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
named ranges - changing ranges with month selected | Excel Programming |