Zip Code Lookup Add-in

G

GoFigure

Does anyone know of an add-in that will return a valid zip code whe
given a street address, city and state
 
B

Bob Phillips

I would have thought the UPS web page was better suited for this job.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
G

GoFigure

Sure but if one has many addresses (20 - 100) and one does this on
weekly basis, it becomes a tedious time-waster.

- Al
 
J

Jim Rech

MS's MapPoint can lookup zip codes. Of course I don't expect you have it
but if you were desperate enough...

Sub TestShowZip()
Dim Street As String
Dim City As String
Dim State As String
Street = "417 Robinson"
City = "North Tonawanda"
State = "NY"
ShowZip Street, City, State
End Sub

Sub ShowZip(Street As String, City As String, State As String)
Dim MApp As MapPoint.Application
Dim MMap As MapPoint.Map
Dim Loc As MapPoint.Location
Set MApp = New MapPoint.Application
Set MMap = MApp.ActiveMap
Set Loc = MMap.FindAddress(Street, City, State)
If Not Loc Is Nothing Then _
MsgBox Loc.StreetAddress.PostalCode
End Sub


--
Jim
message |
| Sure but if one has many addresses (20 - 100) and one does this on a
| weekly basis, it becomes a tedious time-waster.
|
| - Al
|
| Bob Phillips Wrote:
| > I would have thought the UPS web page was better suited for this job.
| >
| > --
| > HTH
| >
| > Bob Phillips
| >
| >
|
|
| --
| GoFigure
| ------------------------------------------------------------------------
| GoFigure's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=4274
| View this thread: http://www.excelforum.com/showthread.php?threadid=493332
|
 
N

Neil Mitchell-Goodson

I have a worksheet here at work which does this. Let me know if you would
like mailed or forwarded.

NMG
 
J

Jim Rech

I do have MapPoint.

How about that? I forgot to mention that you have to set a reference to
MapPoint under Tools, References in the VBE to run my code.

--
Jim
message |
| Neil, if you'd be so kind to email it '[email protected] '
| ([email protected]), I'd like to take a look at it.
|
| Jim, yes, I do have MapPoint. I bought it because I thought it'd be
| better than Streets & Trips. Was I disappointed.
|
| Thanks,
|
| - Al
|
|
| --
| GoFigure
| ------------------------------------------------------------------------
| GoFigure's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=4274
| View this thread: http://www.excelforum.com/showthread.php?threadid=493332
|
 
G

GoFigure

Thanks, Jim.

- Al

Jim said:
How about that? I forgot to mention that you have to set a referenc
to
MapPoint under Tools, References in the VBE to run my code.
Click to expand...
 
N

Neil Mitchell-Goodson

Hi,
Took another look at mine and it doesn't drill down to individual address
level, just area, county, state etc. Sorry!
 
G

GoFigure

Thanks anyway for your offer Neil.

- Al
Hi,
Took another look at mine and it doesn't drill down to individual
address
level, just area, county, state etc. Sorry!
Click to expand...
 
D

Duane Reynolds

This code wors great forfinding zipcodes. can it be modified to return the
zipcode to a cell rather then message box. I have a spreadsheet i do once a
month with 200-300 address missing zips. I need to automate the process of
inputing this data . Thanks for your help
Duane Reynolds
 
D

Dave Peterson

I don't have mappoint installed, so this is untested:

Option Explicit
Dim MApp As MapPoint.Application
Dim MMap As MapPoint.Map
Sub TestShowZip()
Dim myRng As Range
Dim myCell As Range
Dim Street As String
Dim City As String
Dim State As String

With ActiveSheet
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
Street = myCell.Value
City = myCell.Offset(0, 1).Value
State = myCell.Offset(0, 3).Value
myCell.Offset(0, 5).Value = ShowZip(Street, City, State)
Next myCell
End Sub
Function ShowZip(Street As String, City As String, State As String) As String

Dim Loc As MapPoint.Location

If MApp Is Nothing Then
Set MApp = New MapPoint.Application
End If

If MMap Is Nothing Then
Set MMap = MApp.ActiveMap
End If

Set Loc = MMap.FindAddress(Street, City, State)
If Loc Is Nothing Then
ShowZip = ""
Else
ShowZip = Loc.StreetAddress.PostalCode
End If
End Function

You'll have to change how it picks up the city, street and state, though--and
where to put the zipcode.
 
D

Duane Reynolds 322310

Your updated code works perfecly Dave!!!!!!! I can not thank you enough for
your help.

Duane
 
Top