Checking usewr has input a valid address

[Peter Rooney]

Good afternoon all (again)
The object of the exercise is to check that the user has input a valid
address (as I'm FAIRLY sure it's not possible to check if a named range
exists in a workbook without opening it).

Sub ATestForValidRange()
On Error Resume Next
MyValue = InputBox("Enter a value:", "Test for valid range")
Set MyRange = Range(MyValue)
On Error GoTo 0
If Not MyRange Is Nothing Then
MsgBox ("Valid Range")
Else
MsgBox ("Invalid Range")
End If
End Sub

This code functions OK, providing you input a valid range such as a5,
$a$5:$p$26 etc, but if you put in something like "25", it falls over with the
message"Object required" on the "If not myrange is nothing" line, instead of
correctlty branching to display an error message.

Can anyone out there help, please?

Thanks in advance

Pete

 

[Martin]

Try this instead:

If TypeName(myRange) <> "Empty" Then

 

[Peter Rooney]

Martin,

Spot on. Have a green tick and my thanks!

Pete

 

[Tom Ogilvy]

The better way to improve your code is to declare myrange as range, then it
will work as originally intended.

Sub ATestForValidRange()
Dim MyRange as Range
On Error Resume Next
MyValue = InputBox("Enter a value:", "Test for valid range")
Set MyRange = Range(MyValue)
On Error GoTo 0
If Not MyRange Is Nothing Then
MsgBox ("Valid Range")
Else
MsgBox ("Invalid Range")
End If
End Sub

 

[Peter Rooney]

Tom,

That's neat. Thank you, and a green tick for YOU, too!

Regards

Pete