VBA - Use Json

Option Explicit

Sub GETLEIMacro()

    Dim rng As Range, lei As Range
    Dim objRequest As Object
    Dim strUrl As String
    Dim blnAsync As Boolean
    Dim strResponse As String
    Dim strLeiName As String
    Dim rnum As Integer
    
    Set rng = Range("A3:A3000")
    
    rnum = 3
    
    For Each lei In rng
    
        If HasContent(lei) = False Then
            GoTo endloop
        End If
    
        Set objRequest = CreateObject("MSXML2.XMLHTTP")
        strUrl = "https://leilookup.gleif.org/api/v2/leirecords?lei=" + lei
        blnAsync = True
    
        With objRequest
            .Open "GET", strUrl, blnAsync
            .SetRequestHeader "Content-Type", "application/json"
            .Send
            'spin wheels whilst waiting for response
            While objRequest.readyState <> 4
                DoEvents
            Wend
            strResponse = .ResponseText
            
            Dim Result() As String
            Result = Split(strResponse, "$")
            Result = Split(Result(2), "}")
            
            strLeiName = Result(0)
            
            strLeiName = Replace(strLeiName, """:""", "")
            strLeiName = Replace(strLeiName, """", "")
            
            Cells(rnum, 2) = strLeiName
            
        End With
        
        rnum = rnum + 1
        
    Next lei
    
endloop:

End Sub


Public Function HasContent(text_box As Object) As Boolean
    HasContent = (Len(Trim(text_box.Value)) > 0)
End Function