VBA Macro, read range from Sheet2 not working

[yousufj56]

Hi, I have this code for getting JSON data from a specifc URLs that are listed in Sheet2, column A. However, i keep getting an error on the "If" statement. Anyone know how to resolve this?

Public Sub NEWURL()
    Dim https As Object, Json As Object, i As Integer, j As Integer


    Dim Item As Variant


    Set https = CreateObject("MSXML2.XMLHTTP")


    For j = 0 To Sheets("Sheet2").UsedRange.Rows.Count
        If Len(Trim$(Sheets("Sheet2").Range("A" & i).Value2)) > 0 Then
            https.Open "GET", Trim$(Sheets("Sheet2").Range("A" & i).Value2), False
            https.Send


            Set Json = JsonConverter.ParseJson(https.responseText)


            i = 2
            For Each Item In Json.Items
                Sheets("Sheet6").Cells(i, 2).Value = Item
                i = i + 1
            Next Item
        End If
    Next j


    MsgBox ("complete")
End Sub

 

[StephenCrump]

It will probably work better if you change i to j:

For j = 0 To Sheets("Sheet2").UsedRange.Rows.Count
    If Len(Trim$(Sheets("Sheet2").Range("A" & [COLOR=#ff0000][B]i[/B][/COLOR]).Value2)) > 0 Then

.. and also start at j=1 rather than 0.

 

[yousufj56]

Thanks, I changed that but it's still giving me the same error... Do you see anything else wrong there?

 

[Norie]

What is the error?