Find if a range exists?

[tony.reynolds]

hello all,

In my code i need to find if a range exists in my workbook and msgbox"yes"if yes and "no"if no!

i have at the moment a code that results in an error if it dosnt exist (so Im using on error goto statement) but that seems a bit crude

Any help would be appreciated!

 

[Sektor]

Sub F()

    With Worksheets(1).Cells
        If Not .Find(What:="A") Is Nothing Then
            MsgBox "Found!"
        Else
            MsgBox "Not found!"
        End If
    End With

End Sub

 

[VoG]

If you want to check for the existence of a named range try

Function NameExists(theName As String) As Boolean
Dim S As String
On Error GoTo EndFunction
S = ThisWorkbook.Names(theName).RefersTo
NameExists = True
Exit Function
EndFunction:
NameExists = False
End Function

 

[tony.reynolds]

Sub F()
 
    With Worksheets(1).Cells
        If Not .Find(What:="A") Is Nothing Then
            MsgBox "Found!"
        Else
            MsgBox "Not found!"
        End If
    End With
 
End Sub

This finds out if the cell value in the worksheet exists but i want to find out if a range exists..

Like

if Range("ABC") .exists then
Msgbox"found"
Else
msgbox"not found"

 

[pgc01]

Hi Tony

Try:

MsgBox Evaluate("ISREF(ABC)")

 

[tony.reynolds]

Hi Tony

Try:

MsgBox Evaluate("ISREF(ABC)")

hey that is soooo much simpler

thanks guys

and VoG, thanks I now know how the function statment is used!

cheers

 

[Sektor]

Incorrect question. You should say "named range" and not "range".

 

[tony.reynolds]

Incorrect question. You should say "named range" and not "range".

Im sorry

thanks for your help anyhow.