I have
Dim rn as string ' contains name of worksheet
Dim rs as string , contains name of named range

How do I check to see if worksheet rn exists
and named range rs?
Currently trapping err.number but got to be a better way.

Thanks

Found = False
for each ws in Thisworkbook.sheets
if ws.name = rn then
Found = True
exit for
end if
next ws
if found = True then
' enter your code here

end if
Found = False
For Each nm In ActiveWorkbook.Names
If rs = nm.Name Then
found = True
Exit For
End If
Next
If found = True Then
'enter your code here
End If


"treasuresflemar" wrote:

> I have
> Dim rn as string ' contains name of worksheet
> Dim rs as string , contains name of named range
>
> How do I check to see if worksheet rn exists
> and named range rs?
> Currently trapping err.number but got to be a better way.
>
> Thanks
>
>
>

On Error Resume Next
Set sh = Worksheets(rn)
On Error Goto 0
If Not sh Is Nothing Then
...

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"treasuresflemar" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have
> Dim rn as string ' contains name of worksheet
> Dim rs as string , contains name of named range
>
> How do I check to see if worksheet rn exists
> and named range rs?
> Currently trapping err.number but got to be a better way.
>
> Thanks
>
>

Couple of simple ideas:

Sub CheckSheet()
On Error Resume Next
If Worksheets("Sheet2") Is Nothing Then
MsgBox "Sheet Does Not Exist"
Else
MsgBox "Sheet Already Exists"
End If
On Error GoTo 0
End Sub

Sub CheckNames()
Set wbn = ActiveWorkbook.Names
For r = 1 To wbn.Count
If wbn(r).Name = "Test" Then
MsgBox "Name Exists"
End If
Next
End Sub
--
JB


"treasuresflemar" wrote:

> I have
> Dim rn as string ' contains name of worksheet
> Dim rs as string , contains name of named range
>
> How do I check to see if worksheet rn exists
> and named range rs?
> Currently trapping err.number but got to be a better way.
>
> Thanks
>
>
>

Perhaps have a look at

http://www.j-walk.com/ss/excel/tips/tip54.htm

a very valuable source of information!

greetz

"treasuresflemar" <(E-Mail Removed)> schreef in bericht
news:(E-Mail Removed)...
>I have
> Dim rn as string ' contains name of worksheet
> Dim rs as string , contains name of named range
>
> How do I check to see if worksheet rn exists
> and named range rs?
> Currently trapping err.number but got to be a better way.
>
> Thanks
>
>

Thnaks just what the doctor ordered.

"Frederik" <(E-Mail Removed)> wrote in message
news:8Xu5j.240471$(E-Mail Removed)...
> Perhaps have a look at
>
> http://www.j-walk.com/ss/excel/tips/tip54.htm
>
> a very valuable source of information!
>
> greetz
>
> "treasuresflemar" <(E-Mail Removed)> schreef in bericht
> news:(E-Mail Removed)...
>>I have
>> Dim rn as string ' contains name of worksheet
>> Dim rs as string , contains name of named range
>>
>> How do I check to see if worksheet rn exists
>> and named range rs?
>> Currently trapping err.number but got to be a better way.
>>
>> Thanks
>>
>>
>
>