VBA Break For Each Loop

I dont really know what I am doing with VBA, however, I have managed to get something ALMOST working that I want to do!

Basically, I have a worksheet that has a list of gameboy games on the left hand side, and I have managed to fudge togther a macro to grab “A PRICE” from ebay, it uses the following search for example for “Darkwing Duck”

https://www.ebay.co.uk/sch/Video-Games/139973/i.html?LH_Complete=1&LH_Sold=1&_from=R40&_nkw=Darkwing Duck&_dcat=139973&rt=nc&Platform=Nintendo%2520Game%2520Boy


The macro, changes the search fine, and produces a price, however it produces the LAST price on the webpage, and I want the FIRST price, with it being the last sold item. It looks like the macro is finding ALL The prices on the page and stops at the end, I for the life of me cannot get it to stop anywhere but the end, Ive tried Exit For, but to no avail, anyone shed any light?

Below is the macro

As I said, complete novice at this, but seem to be really close to a solution.

Many thanks in advance


Sub Import_Ebay()
    Dim xmlHttp As Object, lastRow As Long
    Dim html As Object, ULs As Object
    lastRow = Columns(“B”).Find(“*”, SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
For i = 5 To lastRow
URL = “https://www.ebay.co.uk/sch/Video-Games/139973/i.html?LH_Complete=1&LH_Sold=1&_from=R40&_nkw=” & Cells(i, 2) & “&_dcat=139973&rt=nc&Platform=Nintendo%2520Game%2520Boy”
        Set xmlHttp = CreateObject(“MSXML2.ServerXMLHTTP.6.0”)
        xmlHttp.Open “GET”, URL, False
        xmlHttp.setRequestHeader “Content-Type”, “text/xml”

    Set html = CreateObject(“htmlfile”)
    html.body.innerHTML = xmlHttp.responseText
    Set ULs = html.getElementsByTagName(“ul”)
    For Each UL In ULs
        If UL.className Like “lvprices *” Then
            For Each hLi In UL.Children
                If hLi.className = “lvprice prc” Then ActiveSheet.Cells(i, 7).Value = hLi.innerText
            Next hLi
        End If
    Next UL
End Sub

By: Simon Walshe

Leave a Reply

Your email address will not be published. Required fields are marked *