Excel VBA address lookup

Question

I am attempting to validate a list of several thousand addresses. I found this code which should compare my spreadsheet data into a google maps api, but I am erroring out on line 'Do While .Cells(r, 1) <> "". Can anyone help me out? I did enter a correct API code "AIzaSyBXhfKRfc2BgWeF5snXswLsvvdUYprhc2k" in that first line.

Const MYKEY = "your_key_goes_here"

Sub BatchGeocode()
With Sheets(1)
    r = 2
    Do While .Cells(r, 1) <> ""
        .Cells(r, 2) = getzip(.Cells(r, 1).Value)

        'the next 4 lines ensure that we don't abuse Google by querying them         too fast
        t = Timer
        Do While Timer < t + 0.3
            DoEvents
        Loop

        r = r + 1
    Loop

End With

MsgBox "Done getting zips"
End Sub


 Function getzip(myAddress As String) As String
 myAddress = Replace(myAddress, " ", "+")
 myURL = "http://maps.google.com/maps/geo?q=" & myAddress &       "&output=xml&oe=utf8&sensor=false&key=" & MYKEY
 Dim objHttp As Object
 Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
Call objHttp.Open("GET", myURL, False)
Call objHttp.Send("")
Results = objHttp.ResponseText

sloc = InStr(Results, "<PostalCodeNumber>") + Len("<PostalCodeNumber>")
eloc = InStr(sloc, Results, "</PostalCodeNumber>")
If eloc > sloc Then getzip = Mid(Results, sloc, eloc - sloc) Else getzip = ""

End Function

"erroring out" looks like what exactly? You will get "type mismatch" if the cell contains an error — Tim Williams, Jan 10 '17 at 17:10
I suspect that "erroring out" is what happens when [you exceed the usage limit](https://developers.google.com/maps/faq#usage-limits) for the maps API. — Comintern, Jan 10 '17 at 17:45

score 0 · Accepted Answer · answered Jan 10 '17 at 17:14

0

Try adding ".Value" to the "Do" line and the line that follows it:

Sub BatchGeocode()
With Sheets(1)
    r = 2
    Do While .Cells(r, 1).Value <> ""
        .Cells(r, 2).Value = getzip(.Cells(r, 1).Value)

Cells(r,1) is an object, not a value. Use .Value to get it's value.

answered Jan 10 '17 at 17:14

Squidx3

92
7

"Value" is the default property for Range: you can leave it off in most cases without causing any problems (but it's good practice to include it) – Tim Williams Jan 10 '17 at 17:22
I am still getting: Run-time error '438' Object doesn't support this property or method – DHuber Jan 10 '17 at 17:25
1

Could your first Sheet be a Chart and not a Worksheet? Try Using the Worksheets collection instead of Sheets collection to avoid charts. – jBuchholz Jan 10 '17 at 17:26
True...didn't think of that. The only other reason I can think of as to why DHuber's code is "erroring out" is that there is no active workbook, so Sheets(1) is not a valid object. – Squidx3 Jan 10 '17 at 17:28
Wow I did have a chart and that was causing the error! When its done I get a message that says "done getting zips" but I don't see them recorded anywhere. Am I missing something! – DHuber Jan 10 '17 at 19:56

Excel VBA address lookup

1 Answers1