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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,806
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,835
Office Version
  1. 365
Platform
  1. Windows
What is the error?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,501
Messages
5,529,240
Members
409,857
Latest member
KailuaTown
Top