Enter Address see if it is in range or not Closed

Question

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

venkat1926 · Answer

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

venkat1926 · Answer

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?

Enter Address see if it is in range or not

2 responses

Similar discussions

Subscribe To Our Newsletter!