Checking usewr has input a valid address
 

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

Checking usewr has input a valid address
 

Try this instead:

If TypeName(myRange) < "Empty" Then


"Peter Rooney" wrote:

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

Checking usewr has input a valid address
 

Martin,

Spot on. Have a green tick and my thanks! :-)

Pete





"Martin" wrote:

Try this instead:

If TypeName(myRange) < "Empty" Then


"Peter Rooney" wrote:

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

Checking usewr has input a valid address
 

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


--
Regards,
Tom Ogilvy



"Peter Rooney" wrote in message
...
Martin,

Spot on. Have a green tick and my thanks! :-)

Pete





"Martin" wrote:

Try this instead:

If TypeName(myRange) < "Empty" Then


"Peter Rooney" wrote:

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

Checking usewr has input a valid address
 

Tom,

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

Regards

Pete



"Tom Ogilvy" wrote:

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


--
Regards,
Tom Ogilvy



"Peter Rooney" wrote in message
...
Martin,

Spot on. Have a green tick and my thanks! :-)

Pete





"Martin" wrote:

Try this instead:

If TypeName(myRange) < "Empty" Then


"Peter Rooney" wrote:

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