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" wrote in message
...
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" schreef in bericht
...
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" wrote in message
...
Perhaps have a look at

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

a very valuable source of information!

greetz

"treasuresflemar" schreef in bericht
...
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