Dynamic URL with cell value

Question

I have code to extract team information using dynamic URL that changes based on the cell value.

I keep getting a run time error "91" saying "Object variable or With block variable not set" and when I debug it points to the line

Set tr_coll = tbl(0).getelementsbytagname("TR")

This code works for other data extractions but for some reason is giving me this error now.

Sub VS()

Dim src As Worksheet, tgt As Worksheet
Dim url As String, team1 As String
Dim team2 As String, j As Integer, row As Integer
Dim XMLHTTP As Object, html As Object
Dim tr_coll As Object, tr As Object
Dim td_coll As Object, td As Object

Set src = Sheets("Match-up")
Set tgt = Sheets("vs")
team1 = src.Range("b1")
team2 = src.Range("aa1")

    url = "http://www.premierleague.com/en-gb/matchday/matches/2015-2016/epl.past-meetings.html/" & team1 & "-vs-" & team2

    Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
    XMLHTTP.Open "GET", url, False
    XMLHTTP.send

    Set html = CreateObject("htmlfile")
    html.body.innerHTML = XMLHTTP.ResponseText

    Set tbl = html.getelementsbytagname("Table")

    Set tr_coll = tbl(0).getelementsbytagname("TR")

    For Each tr In tr_coll
        j = 1
        Set td_col = tr.getelementsbytagname("TD")

        For Each td In td_col
            Cells(row + 1, j).Value = td.innerText
            j = j + 1
        Next
        row = row + 1
    Next
End Sub

Answer 1

It means the tbl variable is set to Nothing. That suggests to me that you have hit a 404 page which doesn't have the tables that you expect.

Make sure that your teams are returning a valid page. It works for me using :

url = "http://www.premierleague.com/en-gb/matchday/matches/2015-2016/epl.past-meetings.html/southampton-vs-west-ham"

Btw, you mispelt td_col with one 'l'(but it doesnt matter since you're not using Option Explicit but be careful of that!!). Also you don't declare your tbl variable which doesnt affect this since you don't use option explicit. It means that you have to declare lots of variables sometimes - which cannot change type, but if you are ever going to code much VBA, I suggest this option.

Step through the code with F8, and see what the team strings are evaluating to. Then hardcode in the resulting string to a browser and see what happens...