VBA Macro, read range from Sheet2 not working

yousufj56

Board Regular
Joined
May 22, 2014
Messages
51
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?

Code:
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,832
Office Version
  1. 365
Platform
  1. Windows
;) It will probably work better if you change i to j:

Code:
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

Board Regular
Joined
May 22, 2014
Messages
51
Thanks, I changed that but it's still giving me the same error... Do you see anything else wrong there?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,923
Office Version
  1. 365
Platform
  1. Windows
What is the error?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,355
Messages
5,547,454
Members
410,792
Latest member
Jabberwokki
Top