Check if string is single cell reference

[andydtaylor]

Hi,

I'd like to check if a user supplied string is single cell reference. My problem is that the below code comes back as vallid if I enter a range like B2:B4.

Can anyone suggest how I could fix this?

Thanks,

Andrew

Sub test2()

Dim UserAdd As String
UserAdd = InputBox("Enter your address")
 
 'check if valid:
If ValidAddress(UserAdd) Then
     MsgBox ("it's valid!")
Else
     MsgBox ("it ain't valid!")
End If

End Sub


Function ValidAddress(strAddress As String) As Boolean
    Dim r As Range
    On Error Resume Next
    Set r = Worksheets(1).Range(strAddress)
    If Not r Is Nothing Then ValidAddress = True
End Function

 

[RoryA]

Try this:

Function ValidAddress(strAddress As String) As Boolean
    Dim r As Range
    On Error Resume Next
    Set r = Worksheets(1).Range(strAddress)
    If Not r Is Nothing Then ValidAddress = (r.Cells.Count = 1)
End Function

 

[andydtaylor]

Awsome, thanks! Works a treat.

 

[andydtaylor]

Hi Rory,

I've just found a scenario where this doesn't work unfortunately. It seems to let me enter fff5/ggg6... it correctly errors on 555555555 or ggggggggg or 67yyy however.

Is there another way to catch this you can suggest?

Thanks,


Andrew

 

[mikerickson]

How about

Function IsSingleCellAddress(inputString As String) As Boolean
On Error Resume Next
    IsSingleStringAddress = (Range(inputString).Cells(1, 1).Address = Range(inputString).Address)
On Error GoTo 0
End Function

 

[andydtaylor]

Hi Mike,

Thanks for that.

Unfortunately using the below in a fresh workbook, it comes up as invalid even if I enter A1 for example. Is there something I can change?

Thanks,


Andrew

Sub test2()

Dim UserAdd As String
UserAdd = InputBox("Enter your address")
 
 'check if valid:
If IsSingleCellAddress(UserAdd) Then
     MsgBox ("it's valid!")
Else
     MsgBox ("it ain't valid!")
End If

End Sub

Function IsSingleCellAddress(inputString As String) As Boolean
On Error Resume Next
    IsSingleStringAddress = (Range(inputString).Cells(1, 1).Address = Range(inputString).Address)
On Error GoTo 0
End Function

 

[mikerickson]

My mistake, when I changed the name in the declaration the function, I should have also changed the name of in the body of the function.

Function IsSingleCellAddress(inputString As String) As Boolean
On Error Resume Next
    IsSingleCellAddress = (Range(inputString).Cells(1, 1).Address = Range(inputString).Address)
On Error GoTo 0
End Function

 

[andydtaylor]

That works brilliantly, thanks!

 

[mikerickson]

You're welcome.

 

[Jonmo1]

How about eliminating the need to test if it's a valid address in the first place...

Sub test2()
Dim UserAdd As Range
 
'This ensures a valid address is selected AND
'The user is able to either SELECT OR TYPE The range
Set UserAdd = Application.InputBox(Prompt:="Enter your address", Type:=8)
 
'This will test if it's a single or multi cell address
If UserAdd.count = 1 Then
    MsgBox "Valid Single Cell Address"
Else
    MsgBox "Valid Multi Cell Address"
End If
End Sub

Hope this helps..