VBA error on "Loop Until" statement

Question

Sorry guys, this is more of an updated question from a previous question... I managed to figure out the issue I was having with regards to clicking the "Next Results" button in IE and looping it back around to pull the same data, then click the "Next Results" button and pull the same data... so on and so on until... and here's where I'm running into the problem. I keep getting an "Object required" error on my "Loop Until" statement... I've tried a number of different workarounds, switching to variables, etc. but still running into errors. Basically I would just like the loop to stop once the "Next Results" button is no longer available (which will obviously happen when there is no more data to pull).

Here's the section of VBA causing the problem...

Dim TDelements As IHTMLElementCollection
Dim TDelement As HTMLTableCell
Dim r As Long

Set TDelements = IE.document.getElementsByTagName("tr")

r = 0

Do
Application.Wait Now + TimeValue("00:00:03")

For Each TDelement In TDelements

    If TDelement.className = "searchActivityResultsContent" Then
        Sheet1.Range("E1").Offset(r, 0).Value = TDelement.ChildNodes(8).innerText
        r = r + 1
    ElseIf TDelement.className = "searchActivityResultsContent" Then
        Sheet1.Range("E1").Offset(r, 0).Value = TDelement.ChildNodes(8).innerText
        r = r + 1
    End If
Next

Application.Wait Now + TimeValue("00:00:02")

Set elems = IE.document.getElementsByTagName("input")
For Each e In elems

If e.Value = "Next Results" Then
    e.Click
    Exit For
End If

Next e

Loop Until e.Value <> "Next Results"

Do Until Not IE.Busy And IE.readyState = 4
DoEvents
Loop

IE.Quit

Here's the HTML code for the buttons I'm referring to...

<table class="contentTable" align="center">
    <tr class="contentTableTR">
<form name="scrollResultListForm" method="post"   action="/scrollTransactionsList.do" onsubmit="return isBusy();">




<input type="submit" name="action" value="Next Results" onmouseover="showComment(event,'Display next results')" onmouseout="hideComment()" class="formButton">


<input type="submit" name="action" value="Last Page" onmouseover="showComment(event,'Goto last result page')" onmouseout="hideComment()" class="formButton">

</form>
</tr>
</table>

move the `until` statement from the end of the loop to the `do` statement at the beginning of the loop to create a `Do Until` loop. — SilentRevolution, Jan 28 '16 at 18:32
@SilentRevolution at the first `Do` `e` will be `nothing`, how enter the loop? and who is upvoting this? sorry, but that is just wrong :/ — Dirk Reichel, Jan 28 '16 at 19:09
add an `On Error Resume Next` that will force the loop start. @DirkReichel, be creative. — SilentRevolution, Jan 28 '16 at 19:38
Moving the Until statement to the Do statement at the beginning unfortunately ends with the same result. — Dick Plixen, Jan 28 '16 at 19:48
@SilentRevolution if no "Next Results" is found, `e` will be nothing and it will end in an error. `On Error Resume Next` will make the loop never stop anymore... which is not a good idea to do... you are still not checking your suggestions :( — Dirk Reichel, Jan 28 '16 at 20:21

Dirk Reichel · Answer 1 · 2016-01-28T20:52:39.063

The first question: Is there any IE.document.getElementsByTagName("input") at the "last" page? (I assume this. But I want to be sure)

Try to change your code a bit:

....
Set elems = IE.document.getElementsByTagName("input")
Dim Next_Loop As Boolean
Next_Loop = True
For Each e In elems

  If e.Value = "Next Results" Then
    e.Click
    Next_Loop = False
    Exit For
  End If

Next e

Loop Until Next_Loop
....

If the For Each loop end, the variable will be empty and this causes the error. Alternatively you could just change the Loop Until e.Value <> "Next Results" line to:

Loop Until e Is Nothing

But this works only if e is declared as an object ;)

EDIT

pls try this:

Dim TDelements As IHTMLElementCollection
Dim TDelement As HTMLTableCell
Dim r As Long, i As Long
Dim e As Object

Set TDelements = IE.document.getElementsByTagName("tr")
r = 0

For i = 1 To 1
Application.Wait Now + TimeValue("00:00:05")

  For Each TDelement In TDelements
    If TDelement.className = "searchActivityResultsContent" Then
      Sheet1.Range("E1").Offset(r, 0).Value = TDelement.ChildNodes(8).innerText
      r = r + 1
    ElseIf TDelement.className = "searchActivityResultsContent" Then
      Sheet1.Range("E1").Offset(r, 0).Value = TDelement.ChildNodes(8).innerText
      r = r + 1
    End If
  Next

  Application.Wait Now + TimeValue("00:00:05")
  Set elems = IE.document.getElementsByTagName("input")

  For Each e In elems
    If e.Value = "Next Results" Then
      e.Click
      i = 0
      Exit For
    End If
  Next e

Next i

Do Until Not IE.Busy And IE.readyState = 4
  DoEvents
Loop
IE.Quit

Thank you for the response, unfortunately adding in the above mentioned code ("Next_Loop") seems to only solve the problem regarding the error, it does click over to the second sheet of data but doesn't pull the data and doesn't continue clicking the "Next Results" button through all the pages. I'm hoping to be able to loop the entire block of code so it pulls the needed data from each sheet until there are no more sheets. — Dick Plixen, Jan 28 '16 at 19:47
Thanks again, your edit does help loop it back around but now when it attempts to pull the data I get the error message "permission denied" on the first "If TDelement..." which doesn't make a lot of sense to me because the first version of code I posted would actually pull the data from the second sheet and give me the "Object Required" error on the loop... I'm starting to feel like I'm in an endless loop of errors. Any other suggestions would be appreciated. — Dick Plixen, Jan 28 '16 at 21:18
Maybe [THIS](http://stackoverflow.com/questions/19933313/vba-internet-explorer-wait-for-web-page-to-load) can help you a bit... It feels like the page isn't finished loading while some actions where done :/ — Dirk Reichel, Jan 28 '16 at 23:36

iDevlop · Answer 2 · 2016-01-28T23:04:25.997

0

Your for each ends when all e are exhausted. Therefore e MUST be Nothing when you arrive on the Loop

edited Jan 28 '16 at 23:04

answered Jan 28 '16 at 19:30

iDevlop

24,841
11
90
149

I'm sorry, I didn't mentioned this in my original post but I'm very much a beginner when it comes to VBA. Could you please elaborate on your answer? How would I apply this suggested fix to my code? Thanks – Dick Plixen Jan 28 '16 at 20:00

VBA error on "Loop Until" statement

2 Answers2