title: formating data and clicking next page in Zillow
body: <p>I am trying to get a VBA webscraping tool to pull the data from Zillow for all sold properties within the last 24 months and paste it in an analytical format. The issues I am running into are:</p>
  <p>1- the data is pulled as HTML format so it is stuffed into a single cell.
  <strong>What I have tried</strong>
  A-tried first to do a blanket convert to colluom  function but the lengths are all variable and it will also sometimes cut off anything past the address.
  B- tried to copy and paste to a .txt file but the path kept breaking somewhere and not all the data made it.</p>
  <p>2- clicking the &quot;Next page&quot; button.
  <strong>What I have tried</strong>
  A-searching href tags to create next url, the keep coming back as &quot;Null&quot; and break the chain
  B- using Css selector to pick the a tag with title &quot;Next page&quot;. I think this is working, but it just keeps clicking the pages without downloading the data after page 1.</p>
  <p>Bonus points if you can:
  -Also download the link href. See point 2 as I keep getting null whenever I try to get the href.</p>
  <p>Make it so the base url pulls from a cell on sheet1 so I can make it more dynamic. Whenever I try it either tells me a specific range is not constant or the object is not valid.</p>
  <p>Any help or clarity on what I am doing wrong would be helpful!
  Code:</p>
  <pre><code>
  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 &quot;GET&quot;, &quot;https://www.zillow.com/the-colony-tx/sold/house_type/3-_beds/&quot;, False
      XMLReq.send
      
      If XMLReq.Status &lt;&gt; 200 Then
          MsgBox &quot;Problem&quot; &amp; vbNewLine &amp; XMLReq.Status &amp; &quot; - &quot; &amp; XMLReq.statusText
          Exit Sub
       End If
       
       Worksheets.Add
       Range(&quot;A1&quot;).Value = &quot;Address&quot;
       Range(&quot;b1&quot;).Value = &quot;Price&quot;
       Range(&quot;c1&quot;).Value = &quot;Bedroom&quot;
       Range(&quot;d1&quot;).Value = &quot;Bath&quot;
       Range(&quot;e1&quot;).Value = &quot;Sqft&quot;
       Range(&quot;f1&quot;).Value = &quot;Date&quot;
       Range(&quot;A2&quot;).Select
       
       HTMLDoc.body.innerhtml = XMLReq.responseText
       Set XMLReq = Nothing
      
       
       
        Set ListCards = HTMLDoc.getElementsByClassName(&quot;list-card-info&quot;)
        
           'Debug.Print ListCards.Length
         
         For Each InfoCard In ListCards
           ActiveCell.Value = InfoCard.innerText
           ActiveCell.Offset(1, 0).Select
           
         Next InfoCard
         
         Range(&quot;f2&quot;).Select
         Set SoldList = HTMLDoc.getElementsByClassName(&quot;list-card-top&quot;)
        '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(&quot;a&quot;)
       
       For Each Zpage In Zpages
          If (Zpage.getAttribute(&quot;title&quot;) = &quot;Next page&quot;) Then
          Zpage.Click
          End If
          Exit For
          Next Zpage
  End Sub
  
  
  </code></pre>