title: Object required Error when error handling
body: <p>The following code retrieves 'dossier Urls' for substances in Column A by scraping the ECHA website. I'm trying to error handle cases where a substance Url cannot be found.</p>
  <p>I can't quite see why the following code fails. I have highlighted the Problematic line with a comment. this is highlighted in debugging as an Object Required error but I can't see where I'm going wrong.</p>
  <pre><code>Sub PopulateExposures()
      Dim url, rw As Range
      
      Set rw = Sheets(&quot;data&quot;).Range(&quot;A2:E2&quot;) 'first row with inputs
      Do While Application.CountA(rw) &gt; 0
          url = SubstanceUrl(rw.Cells(1).Value, rw.Cells(2).Value) 'get the URL
          rw.Cells(5).Resize(1, 3).Value = ExposureData(url) 'get exposure data (as array) and add to row
          Set rw = rw.Offset(1, 0) 'next substance
      Loop
  
  End Sub
  
  Public Function SubstanceUrl(SubstanceName, CASNumber) As String
      
      Const url = &quot;https://echa.europa.eu/information-on-chemicals/registered-substances?&quot; &amp; _
                  &quot;p_p_id=dissregisteredsubstances_WAR_dissregsubsportlet&amp;p_p_lifecycle=1&amp;&quot; &amp; _
                  &quot;p_p_state=normal&amp;p_p_mode=view&amp;&quot; &amp; _
                  &quot;__dissregisteredsubstances_WAR_dissregsubsportlet_javax.portlet.action=dissRegisteredSubstancesAction&quot;
      
      Dim oHTML, oHttp, MyDict, payload, DictKey, sep
      
      Set oHTML = New HTMLDocument
      Set oHttp = CreateObject(&quot;MSXML2.XMLHTTP&quot;)
      Set MyDict = CreateObject(&quot;Scripting.Dictionary&quot;)
      
      MyDict(&quot;_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_name&quot;) = SubstanceName
      MyDict(&quot;_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_cas-number&quot;) = CASNumber
      MyDict(&quot;_disssimplesearchhomepage_WAR_disssearchportlet_disclaimer&quot;) = &quot;true&quot;
      MyDict(&quot;_disssimplesearchhomepage_WAR_disssearchportlet_disclaimerCheckbox&quot;) = &quot;on&quot;
      
      payload = &quot;&quot;
      For Each DictKey In MyDict
          payload = payload &amp; sep &amp; DictKey &amp; &quot;=&quot; &amp; WorksheetFunction.EncodeURL(MyDict(DictKey))
          sep = &quot;&amp;&quot;
      Next DictKey
          
      With oHttp
          .Open &quot;POST&quot;, url, False
          .setRequestHeader &quot;User-Agent&quot;, &quot;Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/84.0.4147.135 Safari/537.36&quot;
          .setRequestHeader &quot;Content-type&quot;, &quot;application/x-www-form-urlencoded&quot;
          .send payload
          oHTML.body.innerHTML = .responseText
      End With
      
      'PROBLEMATIC CODE
      If oHTML.querySelector(&quot;.details&quot;).getAttribute(&quot;href&quot;) Is Error Then
      
      SubstanceUrl = &quot;-&quot;
      Else
      
       'Sometimes output changes despite same input
      SubstanceUrl = oHTML.querySelector(&quot;.details&quot;).getAttribute(&quot;href&quot;)
      
      End If
      
      
      Debug.Print SubstanceUrl
      
      
      
  End Function
  
  Function ExposureData(urlToGet)
      
      Dim XMLReq As New MSXML2.XMLHTTP60
      Dim HTMLDoc As HTMLDocument, dds
      Dim Route(1 To 3) As String, Results(1 To 3) As String, c, Info, Data
      
      Route(1) = &quot;sGeneralPopulationHazardViaInhalationRoute&quot;
      Route(2) = &quot;sGeneralPopulationHazardViaDermalRoute&quot;
      Route(3) = &quot;sGeneralPopulationHazardViaOralRoute&quot;
      
      XMLReq.Open &quot;Get&quot;, urlToGet &amp; &quot;/7/1&quot;, False
      XMLReq.send
       
      If XMLReq.Status &lt;&gt; 200 Then
          Results(1) = &quot;Problem&quot; &amp; vbNewLine &amp; XMLReq.Status &amp; &quot; - &quot; &amp; XMLReq.statusText
      Else
          Set HTMLDoc = New HTMLDocument
          HTMLDoc.body.innerHTML = XMLReq.responseText
          For c = 1 To UBound(Route, 1)
              Set Info = HTMLDoc.getElementById(Route(c))
              If Not Info Is Nothing Then
                  Set Info = Info.NextSibling.NextSibling.NextSibling
                  Set dds = Info.getElementsByTagName(&quot;dd&quot;)
                  If dds.Length &gt; 1 Then
                      Results(c) = dds(1).innerText
                  Else
                      Results(c) = &quot;hazard unknown&quot;
                  End If
              Else
                  Results(c) = &quot;no info&quot;
              End If
          Next c
      End If
      
      ExposureData = Results
      
  End Function
  </code></pre>
  <p>For this code to run values must be present in column A. Acetone and Benzene can be used respectively to test 2 rows.  To test out the error handling Enter something made up like Benzenjaj.</p>
  <p>I think this is a quick fix. Just can't see it.</p>
  <p>Update:</p>
  <p>Tetsing on made up substance name:
  <a href="https://i.stack.imgur.com/wzUge.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wzUge.png" alt="enter image description here" /></a></p>
  <p>First 2 results are found as normal but the made-up chemical causes the following error:</p>
  <p><a href="https://i.stack.imgur.com/HHv7W.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/HHv7W.png" alt="enter image description here" /></a>
  <a href="https://i.stack.imgur.com/FANVv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FANVv.png" alt="enter image description here" /></a></p>
  <ul>
  <li><p><strong>Code:</strong></p>
  <p>Sub PopulateExposures()
  Dim url, rw As Range</p>
  <pre><code> Set rw = Sheets(&quot;data&quot;).Range(&quot;A2:E2&quot;) 'first row with inputs
   Do While Application.CountA(rw) &gt; 0
       url = SubstanceUrl(rw.Cells(1).Value, rw.Cells(2).Value) 'get the URL
       rw.Cells(5).Resize(1, 3).Value = ExposureData(url) 'get exposure data (as array) and add to row
       Set rw = rw.Offset(1, 0) 'next substance
   Loop
  </code></pre>
  <p>End Sub</p>
  <p>Public Function SubstanceUrl(SubstanceName, CASNumber) As String</p>
  <pre><code> Const url = &quot;https://echa.europa.eu/information-on-chemicals/registered-substances?&quot; &amp; _
               &quot;p_p_id=dissregisteredsubstances_WAR_dissregsubsportlet&amp;p_p_lifecycle=1&amp;&quot; &amp; _
               &quot;p_p_state=normal&amp;p_p_mode=view&amp;&quot; &amp; _
               &quot;__dissregisteredsubstances_WAR_dissregsubsportlet_javax.portlet.action=dissRegisteredSubstancesAction&quot;
  
   Dim oHTML, oHttp, MyDict, payload, DictKey, sep
  
   Set oHTML = New HTMLDocument
   Set oHttp = CreateObject(&quot;MSXML2.XMLHTTP&quot;)
   Set MyDict = CreateObject(&quot;Scripting.Dictionary&quot;)
  
   MyDict(&quot;_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_name&quot;) = SubstanceName
   MyDict(&quot;_dissregisteredsubstances_WAR_dissregsubsportlet_disreg_cas-number&quot;) = CASNumber
   MyDict(&quot;_disssimplesearchhomepage_WAR_disssearchportlet_disclaimer&quot;) = &quot;true&quot;
   MyDict(&quot;_disssimplesearchhomepage_WAR_disssearchportlet_disclaimerCheckbox&quot;) = &quot;on&quot;
  
   payload = &quot;&quot;
   For Each DictKey In MyDict
       payload = payload &amp; sep &amp; DictKey &amp; &quot;=&quot; &amp; WorksheetFunction.EncodeURL(MyDict(DictKey))
       sep = &quot;&amp;&quot;
   Next DictKey
  
   With oHttp
       .Open &quot;POST&quot;, url, False
       .setRequestHeader &quot;User-Agent&quot;, &quot;Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/84.0.4147.135 Safari/537.36&quot;
       .setRequestHeader &quot;Content-type&quot;, &quot;application/x-www-form-urlencoded&quot;
       .send payload
       oHTML.body.innerHTML = .responseText
   End With
  
   On Error Resume Next 'ignore error on following line
   SubstanceUrl = oHTML.querySelector(&quot;.details&quot;).getAttribute(&quot;href&quot;)
   On Error GoTo 0      'stop ignoring errors
  
   If Len(SubstanceUrl) = 0 Then SubstanceUrl = &quot;&lt;no URL&gt;&quot;
  </code></pre>
  <p>End Function</p>
  <p>Function ExposureData(urlToGet)</p>
  <pre><code> Dim XMLReq As New MSXML2.XMLHTTP60
   Dim HTMLDoc As HTMLDocument, dds
   Dim Route(1 To 3) As String, Results(1 To 3) As String, c, Info, Data
  
   Route(1) = &quot;sGeneralPopulationHazardViaInhalationRoute&quot;
   Route(2) = &quot;sGeneralPopulationHazardViaDermalRoute&quot;
   Route(3) = &quot;sGeneralPopulationHazardViaOralRoute&quot;
  
   XMLReq.Open &quot;Get&quot;, urlToGet &amp; &quot;/7/1&quot;, False
   XMLReq.send
  
   If XMLReq.Status &lt;&gt; 200 Then
       Results(1) = &quot;Problem&quot; &amp; vbNewLine &amp; XMLReq.Status &amp; &quot; - &quot; &amp; XMLReq.statusText
   Else
       Set HTMLDoc = New HTMLDocument
       HTMLDoc.body.innerHTML = XMLReq.responseText
       For c = 1 To UBound(Route, 1)
           Set Info = HTMLDoc.getElementById(Route(c))
           If Not Info Is Nothing Then
               Set Info = Info.NextSibling.NextSibling.NextSibling
               Set dds = Info.getElementsByTagName(&quot;dd&quot;)
               If dds.Length &gt; 1 Then
                   Results(c) = dds(1).innerText
               Else
                   Results(c) = &quot;hazard unknown&quot;
               End If
           Else
               Results(c) = &quot;no info&quot;
           End If
       Next c
   End If
  
   ExposureData = Results
  </code></pre>
  <p>End Function</p>
  </li>
  </ul>