Run-time error '13': Type Mismatch error on displaying data from JSON Array

Question

I'm getting stuck with the Run-time error '13': Type Mismatch error on displaying data from JSON Array.
Getting stuck at the home3.Cells(vp, 8) = item41("nonVariableListPrice"). I'm trying many different ways and methods by researching google and still unable to solve the problem. May I know if this is a JSON converter problem?
However I'm able to call the other array data from another API without any errors...

Kindly please refer to the below code:

vp = 7


Lastrow2 = Noutput.Cells(Rows.Count, "F").End(xlUp).Row

For id = 2 To Lastrow2 'make a loop call for OpenBasket API
    If Cells(id, 6).Value <> "sn?pshot" Then
            
         Dim strUrl62 As String
        
        'For search quote service API-Step1
        strUrl62 = Cells(id, 6).Value
            
        'create a method for calling HTTP services
        Set hReq62 = CreateObject("MSXML2.XMLHTTP")
                
            With hReq62
                .Open "GET", strUrl62, blnAsync, False
                'Set the headers for bypassing the API service.
                .SetRequestHeader "Authorization", "xxxxxxxxxxxxxxxx" & authKey
                .Send
                While hReq62.ReadyState <> 4
                    DoEvents
                Wend
                
                'wrap the response in a JSON root tag "data" to count returned objects
                strResponse62 = hReq62.ResponseText

            End With
            
        Dim Json61 As Object
        Set Json61 = JsonConverter.ParseJson(strResponse62)
    
    For Each check41 In Json61
        If check41 = "nonVariableListPrice" Then
        
        
        For Each item41 In Json61
            **home3.Cells(vp, 8) = item41("nonVariableListPrice")**
            Debug.Print item41
    
    Next item41
        Else
        
        home3.Cells(vp, 8).Value = "NO DATA"
        
    End If
    
    
    Next check41
    vp = vp + 1

Screenshots:
Error line of code
JSON Structure
Runtime Error '1004'
"Left" unexpected VS "Right"expected Output in Excel

Example JSON:

    {
        "associatedItems": [],
        "associatedTiedItems": [],
        "baseSkuClass": "XZ050",
        "baseSkuClassDescription": "CYBERSENSE SUBSCRIPTION",
        "baseSkuNumber": "210-AXQX",
        "brandId": "1263",
        "buildToStock": false,
        "catalogId": 18,
        "categories": [
            {
                "name": "CONFIG",
                "id": "root",
                "parentCategoryId": "-1",
                "description": "CONFIG"
            },
        ],
        "taxes": [],
        "type": "OrderCode",
        "weight": 0.0,
        "catalogSystemUri": "CFG",
        "allowChangeQuantity": true,
        "familyName": "CyberSense",
        "shipsWith": false,
        "nonVariableListPrice": 147876.912,
        "nonVariableCost": 0.0,
        "catalogCurrency": "USD",
        "nonVariableCurrency": "USD",
        "nonVariableHedgeRateUsed": 1.0,
        "isVariablePriced": true,
        "validationResult": {
            "isValid": true,
            "message": []
        },

Please provide a sample of the JSON, it's not possible to help you without knowing the JSON structure. — Raymond Wu
– Raymond Wu, Commented Aug 29, 2021 at 14:55
Not sure what is your For each item411 in Json61 loop for but try home3.Cells(vp, 8) = Json61(check41) just below If check41 = "nonVariableListPrice" Then @Raiden — Raymond Wu
– Raymond Wu, Commented Aug 29, 2021 at 15:37
Hi @RaymondWu, For each item41 in Json61 is for looping each of the "nonVariableListPrice" from multiple APIs. I'm calling multiple APIs for retrieving the same data from the Object '"nonVariableListPrice"'. Just tried your way home3.Cells(vp, 8) = Json61(check41) just below If check41 = "nonVariableListPrice" Then not getting the data return for the '"nonVariableListPrice"', it went to the Else loop with NO DATA. — Raiden Choong
– Raiden Choong, Commented Aug 29, 2021 at 16:19
Hi @RaymondWu, the issue has been resolved. Apologies for giving the limited data in the first place as I'm very new to StackOverflow. Thanks for your tips and points that were given to me, it means a lot to me! You are right! the "For Each loop" is not required and I'm just creating a redundent loop for check the names of the keys. — Raiden Choong
– Raiden Choong, Commented Aug 30, 2021 at 11:22

Tim Williams · Accepted Answer · 2021-08-29 23:35:11Z

1

item41 is a key in the dictionary Json61 and not itself a Dictionary object, so you get an error here:

home3.Cells(vp, 8) = item41("nonVariableListPrice")

You don't need to loop and check the names of the keys, since Dictionary has an Exists method:

Dim Json61 As Object
Set Json61 = JsonConverter.ParseJson(strResponse62)
    
If Json61.Exists("nonVariableListPrice") Then
    home3.Cells(vp, 8).Value = Json61("nonVariableListPrice")
Else
    home3.Cells(vp, 8).Value = "NO DATA"
End If

Slightly cleaner using a utility function:

home3.Cells(vp, 8).Value = ValueOrDefault(Json61, "nonVariableListPrice", "NO DATA")

Function:

Function ValueOrDefault(dict as object, k, default)
    If dict.Exists(k) Then
        ValueOrDefault = dict(k)
    Else
        ValueOrDefault = default
    End If
End Function

answered Aug 29, 2021 at 23:35

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Raiden Choong Over a year ago

So much thanks! this resolves the problem perfectly. Now all the results are showing accordingly for each of the"nonVariableListPrice" from multiple APIs. That's making sense there is already a for loop for looping each of the APIs to detect the names of the keys in the first place, looks like I'm creating a redundant "For Each" loop to check the names of the keys.. Thanks a lot for the clear explanation and for breaking down the criteria.

Collectives™ on Stack Overflow

Run-time error '13': Type Mismatch error on displaying data from JSON Array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related