Names.add - Test to see if name already exists?

[Craigm]

The intent is if a name does not exist to create it. Then set or change the address it points to.

I am using the line of code below to change the address that the name points to (when it is created by Insert-Name-Define menus).

Names(sFromSheet & aMonths(1) & aYears(i)).RefersTo = "=" & Selection.Address

If the name does not exist it stops with an error.

-------
When I try to test if the name exists:

If Names(sFromSheet & aYears(i)) Then
'Nothing to do it is in existance
Else
Names.Add sFromSheet & aMonths(1) & aYears(i)
End If

I get an Application-Defined or ObjectiDefeined Error on the If statement. I get the same error when I add the worksheet name as a preface.

If Names(sFromSheet & "!" & sFromSheet & aYears(i)) Then
'Nothing to do it is in existance
Else
Names.Add sFromSheet & aMonths(1) & aYears(i)
End If
What am I doing wrong? I am completly lost again! But I am learning!

Thanks in advance- Craigm

[Tom Ogilvy]

The easiest would be

Select.Name = sFromSheet & "!" & sFromSheet & aYears(i)

Which will work whether it exists or not. If it exist, it is redefined. It
if doesn't, it is created.

The way you are going:

Dim nm as Name
On error resume Next
set nm = Thisworkbook.Names(sFromSheet & "!" & sFromSheet & aYears(i))
ON error goto
if nm is nothing then
' Add the name
ThisWorkbook.Names.Add Name:= sFromSheet & "!" & sFromSheet & aYears(i), _
RefersTo:= "=" & selection.Address(External:=True)
else
' it exists, adjust it
nm.RefersTo = "=" & selection.Address(External:=True)
End If

--
Regards,
Tom Ogilvy

"Craigm" wrote:

>
> The intent is if a name does not exist to create it. Then set or change
> the address it points to.
>
> I am using the line of code below to change the address that the name
> points to (when it is created by Insert-Name-Define menus).
>
> Names(sFromSheet & aMonths(1) & aYears(i)).RefersTo = "=" &
> Selection.Address
>
> If the name does not exist it stops with an error.
>
> -------
> When I try to test if the name exists:
>
> If Names(sFromSheet & aYears(i)) Then
> 'Nothing to do it is in existance
> Else
> Names.Add sFromSheet & aMonths(1) & aYears(i)
> End If
>
> I get an Application-Defined or ObjectiDefeined Error on the If
> statement. I get the same error when I add the worksheet name as a
> preface.
>
> If Names(sFromSheet & "!" & sFromSheet & aYears(i)) Then
> 'Nothing to do it is in existance
> Else
> Names.Add sFromSheet & aMonths(1) & aYears(i)
> End If
> What am I doing wrong? I am completly lost again! But I am learning!
>
> Thanks in advance- Craigm
>
>
> --
> Craigm
> ------------------------------------------------------------------------
> Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381
> View this thread: http://www.excelforum.com/showthread...hreadid=537707
>
>

[Bob Phillips]

You could just add it regardless

ThisWorkbook.Names.Add sFromSheet & "!" & aYears(i), "=" &
Selection.Address

this assumes it a worksheet level name that you are creating.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Craigm" <[email protected]> wrote in
message news:[email protected]...
>
> The intent is if a name does not exist to create it. Then set or change
> the address it points to.
>
> I am using the line of code below to change the address that the name
> points to (when it is created by Insert-Name-Define menus).
>
> Names(sFromSheet & aMonths(1) & aYears(i)).RefersTo = "=" &
> Selection.Address
>
> If the name does not exist it stops with an error.
>
> -------
> When I try to test if the name exists:
>
> If Names(sFromSheet & aYears(i)) Then
> 'Nothing to do it is in existance
> Else
> Names.Add sFromSheet & aMonths(1) & aYears(i)
> End If
>
> I get an Application-Defined or ObjectiDefeined Error on the If
> statement. I get the same error when I add the worksheet name as a
> preface.
>
> If Names(sFromSheet & "!" & sFromSheet & aYears(i)) Then
> 'Nothing to do it is in existance
> Else
> Names.Add sFromSheet & aMonths(1) & aYears(i)
> End If
> What am I doing wrong? I am completly lost again! But I am learning!
>
> Thanks in advance- Craigm
>
>
> --
> Craigm
> ------------------------------------------------------------------------
> Craigm's Profile:
http://www.excelforum.com/member.php...o&userid=24381
> View this thread: http://www.excelforum.com/showthread...hreadid=537707
>

[Tom Ogilvy]

Of course

Select.Name = sFromSheet & "!" & sFromSheet & aYears(i)


should have been

Selection.Name = sFromSheet & "!" & sFromSheet & aYears(i)


also if the sheet name has blanks in it, it should be enclosed in single
quotes


Selection.Name = "'" & sFromSheet & "'!" & sFromSheet & aYears(i)


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

>
> The easiest would be
>
> Select.Name = sFromSheet & "!" & sFromSheet & aYears(i)
>
> Which will work whether it exists or not. If it exist, it is redefined. It
> if doesn't, it is created.
>
> The way you are going:
>
> Dim nm as Name
> On error resume Next
> set nm = Thisworkbook.Names(sFromSheet & "!" & sFromSheet & aYears(i))
> ON error goto
> if nm is nothing then
> ' Add the name
> ThisWorkbook.Names.Add Name:= sFromSheet & "!" & sFromSheet & aYears(i), _
> RefersTo:= "=" & selection.Address(External:=True)
> else
> ' it exists, adjust it
> nm.RefersTo = "=" & selection.Address(External:=True)
> End If
>
> --
> Regards,
> Tom Ogilvy
>
> "Craigm" wrote:
>
> >
> > The intent is if a name does not exist to create it. Then set or change
> > the address it points to.
> >
> > I am using the line of code below to change the address that the name
> > points to (when it is created by Insert-Name-Define menus).
> >
> > Names(sFromSheet & aMonths(1) & aYears(i)).RefersTo = "=" &
> > Selection.Address
> >
> > If the name does not exist it stops with an error.
> >
> > -------
> > When I try to test if the name exists:
> >
> > If Names(sFromSheet & aYears(i)) Then
> > 'Nothing to do it is in existance
> > Else
> > Names.Add sFromSheet & aMonths(1) & aYears(i)
> > End If
> >
> > I get an Application-Defined or ObjectiDefeined Error on the If
> > statement. I get the same error when I add the worksheet name as a
> > preface.
> >
> > If Names(sFromSheet & "!" & sFromSheet & aYears(i)) Then
> > 'Nothing to do it is in existance
> > Else
> > Names.Add sFromSheet & aMonths(1) & aYears(i)
> > End If
> > What am I doing wrong? I am completly lost again! But I am learning!
> >
> > Thanks in advance- Craigm
> >
> >
> > --
> > Craigm
> > ------------------------------------------------------------------------
> > Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381
> > View this thread: http://www.excelforum.com/showthread...hreadid=537707
> >
> >

[Craigm]

Thanks to both of you.

Tom, I was struggling with the Selection. part ands was not able to get that to work.. I was trying Names.Select...with my limited knowledge I was not able to deduce Selection.Name.

Bob, I was able to get your suggestion to work but I needed to remove the sFromSheet and "!". Then it works fine. The final code is below.

ThisWorkbook.Names.Add sFromSheet & aYears(i), "=" & Selection.Address

I have moved the ranges around and run the code several times to ensure it works correctly.

Thank you both for the learning experience. I am going to go back and try the Selection method.

Gratefully, Craigm

[Bob Phillips]

I guess that I wrongly assumed it was a worksheet level name. Glad you
sorted it.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Craigm" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks to both of you.
>
> Tom, I was struggling with the Selection. part ands was not able to
> get that to work.. I was trying Names.Select...with my limited
> knowledge I was not able to deduce Selection.Name.
>
> Bob, I was able to get your suggestion to work but I needed to remove
> the sFromSheet and "!". Then it works fine. The final code is below.
>
> ThisWorkbook.Names.Add sFromSheet & aYears(i), "=" & Selection.Address
>
> I have moved the ranges around and run the code several times to ensure
> it works correctly.
>
> Thank you both for the learning experience. I am going to go back and
> try the Selection method.
>
> Gratefully, Craigm
>
>
> --
> Craigm
> ------------------------------------------------------------------------
> Craigm's Profile:
http://www.excelforum.com/member.php...o&userid=24381
> View this thread: http://www.excelforum.com/showthread...hreadid=537707
>