VBA - Determining state from zip code

Hi all,

Sometimes when we receive a large census file, we are only provided the zip code. However, we need to determine the state for records. Using the first (3) digits of the zip code provided this is possible.

For instance: Iowa: 500-529, Minnesota: 550-569, etc. (for all 50 states there is a correspondence)

So, what I need to do:

1) Search for the header "State"
2) IF State is found, skip this process
3) IF it isn't found, run a process that goes through a column where the zip codes are located and be able to determine the state based on the first three digits (refer to up above)
4) Create a new column with State as the header and place it to the right of Zip code, this is where the states would go.

I'd love to explain more if need be.

Thanks everyone.

Originally Posted by tehneXus

Hi,

try this example file: Attachment 242344

contains:

Please Login or Register  to view this content.

Hi there,

I think you might be a little confused (sorry!). I'm uploading just a test file of what it would somewhat look like. When I mentioned the states would have a code (up above) that's not the actual Zip code we get. We get an actual zip code such as 77494, and from that, we must match up the three digits to a range (that's what I listed up above).

So on the file I uploaded it shows what we would see - then if there was no State found, it would add a column to the right and would compare the Zip code given to the Zip code ranges.

We receive these files with thousands of items so you can imagine how a macro would be very helpful for this.

Thanks.

help.xls

It's on paper right now. Give me a minute to type it up, since I will have to anyways and I'm sure that would help.

• ma (010-027)
• ri (028-029)
• nh (030-038)
• ma (039-049)
• vt (050-059(
• ct (060-069)
• nj (070-089)
• ny (100-149)
• pa (150-196)
• de (197-199)
• dc (200-205)
• md (206-219)
• vi (220-246)
• wv (247-269)
• nc (270-289)
• sc (290-299)
• ga (300-319)
• fl (320-349)
• al (350-369)
• tn (370-385)
• ms (386-399)
• ky (400-427)
• oh (430-459)
• in (460-479)
• mi (480-499)
• ia (500-529)
• wi (530-549)
• mn (550-569)
• sd (570-579)
• nd (580-589)
• mo (590-599)
• il (600-629)
• mo (630-659)
• ks (660-679)
• ne (680-699)
• la (700-715)
• ak (716-729)
• ok (730-749)
• tx (750-799)
• co (800-819)
• wy (820-831)
• id (832-839)
• ut (840-849)
• az (850-869)
• nm (870-889)
• nv (890-898)
• ca (900-961)
• hi (967-968)
• or (970-979)
• wa (980-994)
• ak (995-999)

Originally Posted by tehneXus

so 77494 and 77373 would be tx?

You are correct.

Definitely works tehneXus! If I moved the code over to my main macro file and just attached the zip worksheet to the census workbook, everything should work, correct?

Originally Posted by tehneXus

try: Attachment 242355

contains:

Please Login or Register  to view this content.

When I put this code into my main macro, I get a "Subscript out of range" error on this line

Please Login or Register  to view this content.

Originally Posted by Mordred

Did you rename the sheet so that it's name is Zip?

Yes. I added the worksheet "Zip", the one he gave to me in help.xls, to my Census file. The code works standalone in the help.xls, but when added to my main macro is when I get the error.

The census file is always changing (dynamic) so we have the macro save it as a "Census Tester" to make the filename static and then I will copy over the Zip worksheet to "Census Tester" and that's where the code will run.

That's where my problem lies.

I figured it out.. finally!

Thank you both for your help!

Please Login or Register  to view this content.

Originally Posted by Mordred

I'm glad we were able to help you resolve this. Have a great day!

PS: Don't forget to click the star located at the bottom left of each post where a person has helped you. People like their reps around here.

I'd say it is very well deserved reputation. I wouldn't forget to rep you all!

Originally Posted by tehneXus

Hi,

sorry I was busy.
As the "Zip" worksheet will never change (I think) I would save it in a separate workbook together with the code and use "ThisWorkbook" but start the code from the worksheet with the data, otherwise you have to paste it into each workbook you want to match the zips

No problem - I actually worked around having to paste it in. I integrated your code and just tweaked it a bit and now it runs like a charm!