Vba subscript out of range

[Brandnetel]

Hello everyone,

I try to figure out how to get a value (id) from a website url. I've created a table with 2 columns, name and id. I've filled the name column (95 rows) and try to get the id from the website url.

The code below does function (i'm sure it could be done better, but i don't have much vba experience), but somehow it generates a 'subscript out of range' error right after the click event. I've tried it multiple times and the error keeps coming, but every time at another row. Sometimes after 5 rows and if i give it another try, it checks some 24 rows before the error appears.

I've added a couple Application.Wait timeouts. It seems to reduce the amount of out of range errors, but doesn't solve it.

Could you help me out?
My office version is: Office 2013, OS: windows 10

Sub findPlayerId()
    Dim rownumber As Integer
    Dim playerName As String
    Dim worldName As String
    Dim IE As Object
    Dim text As Variant
    Dim url() As String
    
    'Select gameworld
    If Sheets("VriendenCheck").Range("C2") = "Arvahall" Then worldId = "nl1"
    If Sheets("VriendenCheck").Range("C2") = "Brisgard" Then worldId = "nl2"
    If Sheets("VriendenCheck").Range("C2") = "Cirgard" Then worldId = "nl3"
    If Sheets("VriendenCheck").Range("C2") = "Dinegu" Then worldId = "nl4"
    If Sheets("VriendenCheck").Range("C2") = "East-Nagach" Then worldId = "nl5"
    worldName = Sheets("VriendenCheck").Range("C2")
    
    'Count rows in vriendentabel
    rownumber = Sheets("VriendenCheck").ListObjects("VriendenTabel").DataBodyRange.Rows.Count
    
    For i = 1 To rownumber
        With Sheets("VriendenCheck").ListObjects("VriendenTabel")
            'Select friend
            playerName = .DataBodyRange(i, 1).Value
            'Get player id from foestats.com
            Set IE = CreateObject("InternetExplorer.Application")
            IE.Visible = True
            IE.navigate "https://foestats.com/nl/" & worldId & "/players/?server=" & worldId & "&world=" & worldName
            Do Until IE.ReadyState = 4: DoEvents: Loop
            'Wait 1 second
            Application.Wait (Now + TimeValue("0:00:1"))
            'Focus the searchfield
            IE.document.getElementsByTagName("input")(0).Focus
            'Fill searchfield
            For Each text In IE.document.getElementsByTagName("input")
                If text.Name = "" Then
                    text.Value = playerName
                    Application.SendKeys "~" 'Press enter
                End If
            Next
            'Wait 2 seconds
            Application.Wait (Now + TimeValue("0:00:2"))
            'Click player
            IE.document.getElementsByTagName("a")(53).Click
            'Wait 2 seconds
            Application.Wait (Now + TimeValue("0:00:2"))
            'Strip url
            url = Split(IE.LocationURL, "=")
            'Past playerid into excel
            Sheets("VriendenCheck").ListObjects("VriendenTabel").DataBodyRange(i, 2).Value = url(3)
            'Close IE
            IE.Quit
            Application.Wait (Now + TimeValue("0:00:2"))
        End With
    Next i
End Sub

I'm not happy with the Application.SendKeys "~" either, but it does do it's job.

 

[Special-K99]

FYI Subscript out of range also occurs if a worksheet does not exist.