Home error 13 in Inputbox/ Switch case statement
Reply: 0

error 13 in Inputbox/ Switch case statement

user2450
1#
user2450 Published in May 20, 2018, 12:02 pm

I tried to a macro to get top 3 street addresses from a keyword and let the user choose one. However, I kept running into error 13( mismatched data type) on my "case 1" statement or in the last input box. Any help?I am relatively new to VBA.

Sub myTest2()

    Dim xhrRequest As XMLHTTP60
    Dim domDoc As DOMDocument60
    Dim query As String
    Dim myNodes As IXMLDOMNodeList
    Dim myNode As IXMLDOMNode
    Dim nNode As Integer
    Dim re As Range
    Dim result(2) As String


    'allows user to select range
    Set myValue = Application.InputBox(prompt:="Please select the list of addresses wether empty or not of the organizations", Type:=8)
    Set myValueCol = Application.InputBox(prompt:="Please select the column with the names", Type:=8)

    For Each re In myValue
        If IsEmpty(re.Value) Or re.Value = vbNullString Then
            query = Cells(re.Row, myValueCol.Column)
            query = Replace(query, " ", "+")
            query = Replace(query, ",", "%2C")

            'You must acquire a google api key and enter it here
            Dim googleKey As String
            googleKey = "KEY_HERE" 'your api key here

            'Send a "GET" request for place/textsearch
            Set xhrRequest = New XMLHTTP60
            xhrRequest.Open "GET", "https://maps.googleapis.com/maps/api/place/textsearch/xml?query=" & query & "&key=" & googleKey, False
            xhrRequest.send

            'Save the response into a document
            Set domDoc = New DOMDocument60
            domDoc.LoadXML xhrRequest.responseText

            Set myNodes = domDoc.SelectNodes("//result/formatted_address")

            For nNode = 0 To 2

                Set myNode = myNodes(nNode)
                If Not (myNode Is Nothing) Then
                    result(nNode) = myNode.Text
                End If
                If myNode Is Nothing Then
                    result(nNode) = "no additional adresses found"
                End If
            Next nNode
            sinput = Application.InputBox(prompt:="1. " & result(0) & vbNewLine & "2. " & result(1) & vbNewLine & "3. " & result(2), Type:=1)

            Select Case sinput
             Case 1
                re = result0
             Case 2
                re = result1
             Case 3
                re = result2
             End Select

        End If
    Next re

End Sub
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.31134 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO