City & State from Zip Code

Though a number of queries out there, all I have noticed are those that ultimately refer to an internal database that would have to be created and maintained as a solution.

I see that the USPS has a web based API set up and noted another called SmartyStreets (for some reason I associate them with the USPS), but the material goes right over my head.

Does anyone know of any developed procedures that would allow Excel to send query and receive results from resources such as these?

I recognize that I could easily have a huge disconnect here as though I know I can craft a call to the internet, I have no idea if I could receive a response somehow, but that's what makes this forum and you folks so great!

Best Regards,
B-

.
Here is one resource by searching : vba zip code search project

http://www.excelhero.com/blog/2011/0...de-lookup.html

There are probably more out there.


You can use alansidman download or mine, then add a VLOOKUP or other search macro.

https://www.amazon.com/clouddrive/sh...C1I1B0cCEduj53

I see I will have many hours studying Excel Hero in my future. I have used Evaluate ([]) very little, but it appears I will want to better understand its full scope due to the constant usage in the example; I think I now have a grasp on

[offset(output,1,1,1,1)]

and I never thought of using a statement such as

If Len(rZip) Then

!

If y'all are still out there and following, would you be so kind as to explain why in the following code they use "With Target.Parent.Hyperlinks"? Maybe I'm missing the boat, for I see nowhere that a hyperlink is used (stop laughing...)-

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim sState As String
    
    If Target.Cells.Count > 1 Then Exit Sub                     'Only one cell selected
    If Intersect(Target, [MyList]) Is Nothing Then Exit Sub     'Cell is inside the evaluation area
    If Target = vbNullString Then                               'Cell has information
        If Intersect(Target, [MyList[Zip]]) Is Nothing Then     'Cell is in the Zip column of evaluation area
            Exit Sub
        End If
    End If
    
    With Target.Parent.Hyperlinks
        Select Case False
            Case Intersect(Target, [MyList[Zip]]) Is Nothing
                If Target = vbNullString Then
                    sState = vbNullString
                    Intersect(Target.EntireRow, [MyList[City]]) = vbNullString
                Else
                    ZipCodeFilter Target
                    sState = [offset(output,1,2,1,1)]
                    If [counta(offset(output,1,1,2,1))] = 1 Then
                        With Intersect(Target.EntireRow, [MyList[City]])
                            .Value = [offset(output,1,1,1,1)]
                        End With
                    Else
                        Intersect(Target.EntireRow, [MyList[City]]) = vbNullString
                    End If
                End If
                Intersect(Target.EntireRow, [MyList[State]]) = sState
            Case Else
        
        End Select
    End With
    
End Sub

Much learning from the example going on, and I am sure there will be a lot going through the site's blog!...

I know I am imposing on your time; if and when you can respond I appreciate it-

.
For an online search, the URL you posted in the original thread gives step by step instructions how and what to use to create a workbook with macros to perform that action (online query).

I wouldn't be much assistance to you because I know nothing about it. There are others here on this FORUM and other forums who are proficient in the area. Hopefully someone can step forward and
assist you.

@Logit: Yes, but from the link you gave me, can you advise what the function is of that "With Target.Parent.Hyperlinks" statement? I see no hyperlinks to be used!

.
Not certain this is any clearer than mud (as indicated, I know nothing about web scraping):

http://help.clickdimensions.com/faqs-1/

.

-Wouldn't be the first time I used code that made no sense to me

I do that also ... but I cant advise someone else how to use confusing code ... it's too confusing.