Enter Address see if it is in range or not

[Closed]
Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
I want to be able for people to type a street address and see if that address is available from a list of data that has street range and street name in two columns like this.

Min Range Max Range Street Name
1 200 Maple st
2 1589 Oak st
3 10000 Main St
4 30 Brown St

I have it setup where a box says "enter the street name" then check if that street is in the list of streets that i have out to the side of the information above. If that street is in the list then check the street number which i will also prompt them to put in and see if it is in the range. So if they type in "100 Maple St" it will tell me if the street is there and if the number is within range. any help would be appreciated. Thanks Don

2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
800
your data is like this column A

address
200 Maple st
1589 Oak st
10000 Main St
30 Brown St

Put a button on sheet 1 and name it as you like it.
assign the following macro to that button
if the macro serves your purpose POST CONFIRMTION

Sub test()
Dim r As Range, cfindx As Range, x As String, y, cfindy As Range
On Error Resume Next
Set r = Range(Range("A2"), Range("A2").End(xlDown))
x = InputBox("type the street name eg.maple st")
y = InputBox("type the door number e.g. 200")

Set cfindx = r.Cells.Find(what:=x, lookat:=xlPart)
If Not cfindx Is Nothing Then
MsgBox "the street name is available"
GoTo findnumber
Else
MsgBox "the street name is not available"
End If
findnumber:
Set cfindy = cfindx.Find(what:=y, lookat:=xlPart)
If Not cfindy Is Nothing Then
MsgBox "the number and street name is available in  " & cfindx.Address

Else
MsgBox "that address is not avilable in the range"
End If

End Sub
I poorly described my setup to you and I'm sorry. I'll try to explain better. I think my columns i typed in ran together a little bit. Here's is the information I have.

Column A Column B Column C Column D
Street Name Lowest Street Number Highest Street number Services Available

Maple St 100 1500 Service1
Maple St 1501 2000 Service3
Brown St 1 1000000 Service2

The above is what i'm working with. If they enter address "500 Maple St" I need it to find the street and then check if the address number is between B1 and C1 and if it does display Column D. Also, sometimes there will be more than one street with different ranges of street numbers because they can get different services.

So, if they type the address in it needs to say "Number and Address in Range" then display Column D corresponding to the row. Sorry again. I couldn't get the Macro you sent me to work, but i had my information wrong. Thanks for your help.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
800
quote
then display Column D corresponding to the row
unquote
not clear. for e.g. D1 already has entry service 1. what is to display in D1?

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!