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