Dynamically looping over JSON-request with index numbers in key

Question

I have written the following VBA-macro:

Sub getLineUps()

Dim lastRow As Long
Dim strURL As String
Dim strJSON As String 
Dim strMatchID As String
Dim strIndex As Integer
Dim i, x As Integer
Dim Json As Object


lastRow = Range("A8").CurrentRegion.Rows.Count

For x = 9 To lastRow
   strMatchID = Cells(9, 1).Value
   strURL = "https://api.football-data.org/v2/matches/" & strMatchID
   Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
   MyRequest.Open "Get", strURL
   MyRequest.setRequestHeader "X-Auth-Token", "personal_password"
   MyRequest.Send
   Set Json = JsonConverter.ParseJson(MyRequest.ResponseText)

   For Each Item In Json("match")("homeTeam")("lineup")
      Cells(9, 11).Value = Item(0)("name")
      Cells(9, 12).Value = Item(1)("name")
      Cells(9, 13).Value = Item(2)("name")
      Cells(9, 14).Value = Item(3)("name")
      Cells(9, 15).Value = Item(4)("name")
      Cells(9, 16).Value = Item(5)("name")
      Cells(9, 17).Value = Item(6)("name")
      Cells(9, 18).Value = Item(7)("name")
      Cells(9, 19).Value = Item(8)("name")
      Cells(9, 20).Value = Item(9)("name")
      Cells(9, 21).Value = Item(10)("name")
   Next
Next

End Sub

Running this macro gives me the following error-message: Run-time error '13': Type Mismatch on the following line: Cells(9, 11).Value = Item(0)("name") The output of my JSON-request looks as follows:

{
  'head2head': {
    'numberOfMatches': 7,
    'totalGoals': 19,
    'homeTeam': {
      'wins': 1,
      'draws': 1,
      'losses': 5
    },
    'awayTeam': {
      'wins': 5,
      'draws': 1,
      'losses': 1
    }
  },
  'match': {
    'id': 233132,
    'competition': {
      'id': 2021,
      'name': 'Premier League'
    },
    'season': {
      'id': 151,
      'startDate': '2018-08-10',
      'endDate': '2019-05-12',
      'currentMatchday': 11,
      'winner': None,
      'availableStages': [
        'REGULAR_SEASON'
      ]
    },
    'utcDate': '2018-11-03T12:30:00Z',
    'status': 'FINISHED',
    'minute': None,
    'attendance': 10792,
    'venue': 'Vitality Stadium',
    'matchday': 11,
    'stage': 'REGULAR_SEASON',
    'group': 'Regular Season',
    'lastUpdated': '2018-11-05T05:03:52Z',
    'score': {
      'winner': 'AWAY_TEAM',
      'duration': 'REGULAR',
      'fullTime': {
        'homeTeam': 1,
        'awayTeam': 2
      },
      'halfTime': {
        'homeTeam': 1,
        'awayTeam': 1
      },
      'extraTime': {
        'homeTeam': None,
        'awayTeam': None
      },
      'penalties': {
        'homeTeam': None,
        'awayTeam': None
      }
    },
    'homeTeam': {
      'id': 1044,
      'name': 'AFC Bournemouth',
      'coach': {
        'id': 11594,
        'name': 'Eddie Howe',
        'countryOfBirth': 'England',
        'nationality': 'England'
      },
      'captain': {
        'id': 8232,
        'name': 'Simon Francis',
        'shirtNumber': 2
      },
      'lineup': [
        {
          'id': 8247,
          'name': 'Ryan Fraser',
          'position': 'Midfielder',
          'shirtNumber': 24
        },
        {
          'id': 3327,
          'name': 'Lewis Cook',
          'position': 'Midfielder',
          'shirtNumber': 16
        },
        {
          'id': 8229,
          'name': 'Asmir Begović',
          'position': 'Goalkeeper',
          'shirtNumber': 27
        },
        {
          'id': 3737,
          'name': 'Jefferson Lerma',
          'position': 'Midfielder',
          'shirtNumber': 8
        },
        {
          'id': 8231,
          'name': 'Adam Smith',
          'position': 'Defender',
          'shirtNumber': 15
        },
        {
          'id': 8240,
          'name': 'Nathan Aké',
          'position': 'Defender',
          'shirtNumber': 5
        },
        {
          'id': 8251,
          'name': 'Callum Wilson',
          'position': 'Attacker',
          'shirtNumber': 13
        },
        {
          'id': 4321,
          'name': 'David Brooks',
          'position': 'Attacker',
          'shirtNumber': 20
        },
        {
          'id': 8232,
          'name': 'Simon Francis',
          'position': 'Defender',
          'shirtNumber': 2
        },
        {
          'id': 8234,
          'name': 'Steve Cook',
          'position': 'Defender',
          'shirtNumber': 3
        },
        {
          'id': 8242,
          'name': 'Junior Stanislas',
          'position': 'Midfielder',
          'shirtNumber': 19
        }
      ],
      'bench': [
        {
          'id': 8227,
          'name': 'Artur Boruc',
          'position': 'Goalkeeper',
          'shirtNumber': 1
        },
        {
          'id': 8233,
          'name': 'Charlie Daniels',
          'position': 'Defender',
          'shirtNumber': 11
        },
        {
          'id': 8246,
          'name': 'Andrew Surman',
          'position': 'Midfielder',
          'shirtNumber': 6
        },
        {
          'id': 8243,
          'name': 'Dan Gosling',
          'position': 'Midfielder',
          'shirtNumber': 4
        },
        {
          'id': 8255,
          'name': 'Jermain Defoe',
          'position': 'Attacker',
          'shirtNumber': 18
        },
        {
          'id': 8254,
          'name': 'Jordon Ibe',
          'position': 'Attacker',
          'shirtNumber': 10
        },
        {
          'id': 8253,
          'name': 'Lys Mousset',
          'position': 'Attacker',
          'shirtNumber': 9
        }
      ]
    },
    'awayTeam': {
      'id': 66,
      'name': 'Manchester United FC',
      'coach': {
        'id': 11613,
        'name': 'José Mourinho',
        'countryOfBirth': 'Portugal',
        'nationality': 'Portugal'
      },
      'captain': {
        'id': 3317,
        'name': 'Ashley Young',
        'shirtNumber': 18
      },
      'lineup': [
        {
          'id': 3188,
          'name': 'David De Gea',
          'position': 'Goalkeeper',
          'shirtNumber': 1
        },
        {
          'id': 7909,
          'name': 'Mata',
          'position': 'Attacker',
          'shirtNumber': 8
        },
        {
          'id': 7896,
          'name': 'Chris Smalling',
          'position': 'Defender',
          'shirtNumber': 12
        },
        {
          'id': 7911,
          'name': 'Alexis Sánchez',
          'position': 'Attacker',
          'shirtNumber': 7
        },
        {
          'id': 3372,
          'name': 'Anthony Martial',
          'position': 'Attacker',
          'shirtNumber': 11
        },
        {
          'id': 7898,
          'name': 'Luke Shaw',
          'position': 'Defender',
          'shirtNumber': 23
        },
        {
          'id': 3492,
          'name': 'Victor Nilsson-Lindelöf',
          'position': 'Defender',
          'shirtNumber': 2
        },
        {
          'id': 3317,
          'name': 'Ashley Young',
          'position': 'Midfielder',
          'shirtNumber': 18
        },
        {
          'id': 3438,
          'name': 'Nemanja Matić',
          'position': 'Midfielder',
          'shirtNumber': 31
        },
        {
          'id': 3366,
          'name': 'Paul Pogba',
          'position': 'Midfielder',
          'shirtNumber': 6
        },
        {
          'id': 3232,
          'name': 'Fred',
          'position': 'Midfielder',
          'shirtNumber': 17
        }
      ],
      'bench': [
        {
          'id': 3202,
          'name': 'Sergio Romero',
          'position': 'Goalkeeper',
          'shirtNumber': 22
        },
        {
          'id': 7897,
          'name': 'Phil Jones',
          'position': 'Defender',
          'shirtNumber': 4
        },
        {
          'id': 7900,
          'name': 'Matteo Darmian',
          'position': 'Defender',
          'shirtNumber': 36
        },
        {
          'id': 3325,
          'name': 'Jesse Lingard',
          'position': 'Midfielder',
          'shirtNumber': 14
        },
        {
          'id': 7904,
          'name': 'Ander Herrera',
          'position': 'Midfielder',
          'shirtNumber': 21
        },
        {
          'id': 7905,
          'name': 'Scott McTominay',
          'position': 'Midfielder',
          'shirtNumber': 39
        },
        {
          'id': 3331,
          'name': 'Marcus Rashford',
          'position': 'Attacker',
          'shirtNumber': 10
        }
      ]
    },
    'goals': [
      {
        'minute': 11,
        'extraTime': None,
        'type': 'REGULAR',
        'team': {
          'id': 1044,
          'name': 'AFC Bournemouth'
        },
        'scorer': {
          'id': 8251,
          'name': 'Callum Wilson'
        },
        'assist': {
          'id': 8242,
          'name': 'Junior Stanislas'
        }
      },
      {
        'minute': 35,
        'extraTime': None,
        'type': 'REGULAR',
        'team': {
          'id': 66,
          'name': 'Manchester United FC'
        },
        'scorer': {
          'id': 3372,
          'name': 'Anthony Martial'
        },
        'assist': {
          'id': 7911,
          'name': 'Alexis Sánchez'
        }
      },
      {
        'minute': 90,
        'extraTime': 2,
        'type': 'REGULAR',
        'team': {
          'id': 66,
          'name': 'Manchester United FC'
        },
        'scorer': {
          'id': 3331,
          'name': 'Marcus Rashford'
        },
        'assist': None
      }
    ],
    'bookings': [
      {
        'minute': 54,
        'team': {
          'id': 66,
          'name': 'Manchester United FC'
        },
        'player': {
          'id': 7898,
          'name': 'Luke Shaw'
        },
        'card': 'YELLOW_CARD'
      },
      {
        'minute': 58,
        'team': {
          'id': 1044,
          'name': 'AFC Bournemouth'
        },
        'player': {
          'id': 3737,
          'name': 'Jefferson Lerma'
        },
        'card': 'YELLOW_CARD'
      },
      {
        'minute': 63,
        'team': {
          'id': 1044,
          'name': 'AFC Bournemouth'
        },
        'player': {
          'id': 8242,
          'name': 'Junior Stanislas'
        },
        'card': 'YELLOW_CARD'
      },
      {
        'minute': 64,
        'team': {
          'id': 1044,
          'name': 'AFC Bournemouth'
        },
        'player': {
          'id': 8251,
          'name': 'Callum Wilson'
        },
        'card': 'YELLOW_CARD'
      },
      {
        'minute': 82,
        'team': {
          'id': 66,
          'name': 'Manchester United FC'
        },
        'player': {
          'id': 3317,
          'name': 'Ashley Young'
        },
        'card': 'YELLOW_CARD'
      },
      {
        'minute': 83,
        'team': {
          'id': 1044,
          'name': 'AFC Bournemouth'
        },
        'player': {
          'id': 8231,
          'name': 'Adam Smith'
        },
        'card': 'YELLOW_CARD'
      },
      {
        'minute': 90,
        'team': {
          'id': 66,
          'name': 'Manchester United FC'
        },
        'player': {
          'id': 3331,
          'name': 'Marcus Rashford'
        },
        'card': 'YELLOW_CARD'
      }
    ],
    'substitutions': [
      {
        'minute': 56,
        'team': {
          'id': 66,
          'name': 'Manchester United FC'
        },
        'playerOut': {
          'id': 7909,
          'name': 'Mata'
        },
        'playerIn': {
          'id': 3331,
          'name': 'Marcus Rashford'
        }
      },
      {
        'minute': 56,
        'team': {
          'id': 66,
          'name': 'Manchester United FC'
        },
        'playerOut': {
          'id': 3232,
          'name': 'Fred'
        },
        'playerIn': {
          'id': 7904,
          'name': 'Ander Herrera'
        }
      },
      {
        'minute': 68,
        'team': {
          'id': 1044,
          'name': 'AFC Bournemouth'
        },
        'playerOut': {
          'id': 3327,
          'name': 'Lewis Cook'
        },
        'playerIn': {
          'id': 8243,
          'name': 'Dan Gosling'
        }
      },
      {
        'minute': 74,
        'team': {
          'id': 1044,
          'name': 'AFC Bournemouth'
        },
        'playerOut': {
          'id': 8247,
          'name': 'Ryan Fraser'
        },
        'playerIn': {
          'id': 8254,
          'name': 'Jordon Ibe'
        }
      },
      {
        'minute': 78,
        'team': {
          'id': 66,
          'name': 'Manchester United FC'
        },
        'playerOut': {
          'id': 7911,
          'name': 'Alexis Sánchez'
        },
        'playerIn': {
          'id': 3325,
          'name': 'Jesse Lingard'
        }
      },
      {
        'minute': 90,
        'team': {
          'id': 1044,
          'name': 'AFC Bournemouth'
        },
        'playerOut': {
          'id': 8242,
          'name': 'Junior Stanislas'
        },
        'playerIn': {
          'id': 8246,
          'name': 'Andrew Surman'
        }
      }
    ],
    'referees': [
      {
        'id': 11520,
        'name': 'Paul Tierney',
        'nationality': None
      },
      {
        'id': 11606,
        'name': 'Constantine Hatzidakis',
        'nationality': None
      },
      {
        'id': 11576,
        'name': 'Darren Cann',
        'nationality': None
      },
      {
        'id': 11487,
        'name': 'Kevin Friend',
        'nationality': None
      }
    ]
  }
}

Although, this output doesn't show it, we need an index number. I observed this by inspecting the output with Python.

&

What is exactly going wrong with my VBA-macro as it doesn't show the desired output? In addition, how can I re-write the macro in the following way:

previous code
...

Set Json = JsonConverter.ParseJson(MyRequest.ResponseText)
i = 1
For Each Item In Json("match")("homeTeam")("lineup")
  Cells(9, i).Value = Item(indexnumber??)("name")
  i = i + 1 

How to correctly specify the index-number in this version?

Thank you very very much

Solution with the help QHarr

Option Explicit
Public Sub ReadValues()
Dim fso As Object, stream As Object, jsonText, jsonInput As String, item As 
Object
Dim json As Object, ws As Worksheet, i As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set fso = CreateObject("Scripting.FileSystemObject")
Set stream = fso.OpenTextFile("C:\Users\HJA\Desktop\Betting\line_up.json", 
ForReading)
jsonText = stream.ReadAll

ws.Cells(1, 1).Value = jsonText
ws.Cells(7,1).Formula = "=SUBSTITUTE(SUBSTITUTE(A1,A2,A3),A5,A6)"
jsonInput = Cells(7, 1).Value
MsgBox jsonInput

Set json = JsonConverter.ParseJson(jsonInput)("match")("homeTeam")("lineup") 'collection of dictionaries
i = 1
For Each item In json
   ws.Cells(9, i).Value = item("name")
   i = i + 1
Next

End Sub

Answer 1

After you have corrected the JSON by swopping ' for "" and wrapped None as "None" you can use the following to get the names. Note I am reading the JSON from a file. You would cut that bit out and replace with

Set Json = JsonConverter.ParseJson(MyRequest.ResponseText)("match")("homeTeam")("lineup")

You are dealing with a collection of dictionaries so you access the name by key during the loop (each item is a dictionary not a collection).

Put your CreateObject("WinHttp.WinHttpRequest.5.1") before the loop. There is no point in continually recreating and destroying the object. Create it once and leave to go out of scope at the end of the sub.

If you have any blank rows in the data area using currentRegion could return the wrong end row.

Option Explicit
Public Sub ReadValues()
    Dim fso As Object, stream As Object, jsonText As String, item As Object
    Dim json As Object, ws As Worksheet, i As Long
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set stream = fso.OpenTextFile("C:\Users\User\Desktop\test.json", ForReading)
    jsonText = stream.ReadAll
    stream.Close

    Set json = JsonConverter.ParseJson(jsonText)("match")("homeTeam")("lineup") 'collection of dictionaries
    i = 1
    For Each item In json
       ws.Cells(9, i).Value =  item("name")
       i = i + 1
    Next
End Sub