Option Explicit

Sub GetZillowSold()

    Dim XMLReq As New MSXML2.XMLHTTP60
    Dim HTMLDoc As New MSHTML.HTMLDocument

    Dim ListCards As MSHTML.IHTMLElementCollection
    Dim InfoCard As MSHTML.IHTMLElement

    Dim SoldList As MSHTML.IHTMLElementCollection
    Dim SoldDate As MSHTML.IHTMLElement

    Dim Zpages As MSHTML.IHTMLElementCollection
    Dim Zpage As MSHTML.IHTMLElement

    Dim CardID As Integer

    XMLReq.Open "GET", "https://www.zillow.com/the-colony-tx/sold/house_type/3-_beds/", False
    XMLReq.send

    If XMLReq.Status <> 200 Then
        MsgBox "Problem" & vbNewLine & XMLReq.Status & " - " & XMLReq.statusText
        Exit Sub
     End If

     Worksheets.Add
     Range("A1").Value = "Address"
     Range("b1").Value = "Price"
     Range("c1").Value = "Bedroom"
     Range("d1").Value = "Bath"
     Range("e1").Value = "Sqft"
     Range("f1").Value = "Date"
     Range("A2").Select

     HTMLDoc.body.innerhtml = XMLReq.responseText
     Set XMLReq = Nothing



      Set ListCards = HTMLDoc.getElementsByClassName("list-card-info")

         'Debug.Print ListCards.Length

       For Each InfoCard In ListCards
         ActiveCell.Value = InfoCard.innerText
         ActiveCell.Offset(1, 0).Select

       Next InfoCard

       Range("f2").Select
       Set SoldList = HTMLDoc.getElementsByClassName("list-card-top")
      'Debug.Print SoldList.Length,

      For Each SoldDate In SoldList
         ActiveCell.Value = Mid(SoldDate.innerText, 6)
         ActiveCell.Offset(1, 0).Select
     Next SoldDate

    Set Zpages = HTMLDoc.getElementsByTagName("a")

     For Each Zpage In Zpages
        If (Zpage.getAttribute("title") = "Next page") Then
        Zpage.Click
        End If
        Exit For
        Next Zpage
End Sub