VBA CODE: Retrieve City and State using ZipCode.

Question

Hi all,

Is there a way to access GOOGLE's or USPS'  ZIPCODE database using the ZipCode to return and populate  City and State in the EXCEL UserForm?

I am new to VBA and Userform.  I have a lot of address info to type in and thought it would be nice if this were doable?

Thanks!

Answer 1

hi

sorry about delay. don't really understand your suggestion. i am a VBA newbie. 

thanks though!

Answer 2

Hi,

step1

download the file and save as xlsm

close and reopen the file

step2

convert all data in a new sheet and in columns  A-D

in a regular module write the below vba macro

Sub macro_01()

Set ws = Sheets.Add(after:=Sheets(1))

Dim r As Long, r1 As Long

r = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row

Sheets(1).Range("A1:D" & r).Copy

ws.Cells(1, 1).PasteSpecial xlPasteValues

ws.Cells(1, 1).PasteSpecial xlPasteFormats

r1 = Sheets(1).Cells(Rows.Count, "F").End(xlUp).Row

Sheets(1).Range("F2:I" & r1).Copy

ws.Cells(r + 1, 1).PasteSpecial xlPasteValues

ws.Cells(r + 1, 1).PasteSpecial xlPasteFormats

Application.CutCopyMode = False

ActiveSheet.UsedRange.EntireColumn.AutoFit

End Sub

now,

you can add autofilter or add a pivot table

or

convert range to Table

1st section

last section

Answer 3

hi

saw that ZIPCODE is formatted as TEXT in the file so changed my code to below but still getting MISMATCH type 13 erroror.

again, thanks!

Private Sub txtZip_Change()

Dim FindValue As String

Dim FindWhat1 As String * 5

If Len(txtZip.Text) >= 5 Then

FindWhat1 = Left(txtZip.Text, 5)

FindValue = Application.VLookup(Trim(FindWhat1), Sheet2.Range("A1:C74022"), 2, False)

txtCity.Text = FindValue

End If

End Sub

Answer 4

hi

actually was trying that approach first with a similar file i found using  code below.  keep getting MISMATCH (13) error. and i saw also saw posted somewhere that since a lot of duplicate zipcodes the approach won't work anyway.

how do you access this file in your code?

thank you very much though for the quick reply. :)

---

Private Sub txtZip_Change()

Dim FindWhat1 As String * 5

Dim FindWhat2 As Integer

Dim FindValue As String

If Len(txtZip.Text) >= 5 Then

FindWhat1 = Left(txtZip.Text, 5) + 0

FindWhat2 = Val(FindWhat1)

FindWhat2 = Format(FindWhat2, "00000")

FindValue = Application.VLookup(FindWhat2, Sheet2.Range("A1:C76782"), 2, False)

txtCity.Text = FindValue

End If

End Sub

Answer 5

take a look here..

http://www.google.gr/url?sa=t&rct=j&q=&esrc=s&source=web&cd=4&ved=0ahUKEwjgu6-5grnKAhVBchQKHeU1BlAQFgg2MAM&url=http%3A%2F%2Fwww.pier2pier.com%2Flinks%2Ffiles%2FCountrystate%2FUSA-Zip.xls&usg=AFQjCNEr6-_lQG2vyuDT5kY_XJqpdzYYQA&sig2=0JxzCQpC6RKabiZsIatAUQ