Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names.add - Test to see if name already exists?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names.add - Test to see if name already exists?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names.add - Test to see if name already exists?
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names.add - Test to see if name already exists?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names.add - Test to see if name already exists?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names.add - Test to see if name already exists?
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Test if file exists | Excel Discussion (Misc queries) | |||
How to test if a DLL (library) exists | Excel Programming | |||
Test for Worksheet Exists | Excel Programming | |||
Test if a folder exists, create if it doesn't? | Excel Programming | |||
Test if a folder exists | Excel Programming |