VBA Macro For next loop

Question

I'm a noob at coding. I got help creating the following code. However, I need to create a for and next loop. Basically, URL = Sheets("Sheet2").Range(A:A)

The URL needs to change in every loop. The URL will be listed from A1 and down to some A(X).

I've heard its very easy to do but i've been spending a few hours trying to learn it and its way beyond my skills...

Sub Test7()
    'Haluk
    '11/12/2017
    
    Dim objHTTP As Object
    Dim MyScript As Object
    Dim myData As Variant
    Dim myLength As Byte
    
    'Clean the sheet
    
    ActiveSheet.Cells.Clear

    URL = "https://min-api.cryptocompare.com/data/histominute?fsym=BTC&tsym=USD&limit=60&aggregate=3&e=CCCAGG"
    
    'The returned JSon table contents have the primary key/label named as "Data"
    'We are going to refer this "Data" in the following two JScripts "getValue" and "getLength"
    
    Set MyScript = CreateObject("MSScriptControl.ScriptControl")
    MyScript.Language = "JScript"
    MyScript.AddCode "function getValue(JSonList, JItem, JSonProperty) { return JSonList.Data[JItem][JSonProperty]; }"
    MyScript.AddCode "function getLength(JSonList) { return JSonList.Data.length; }"
    
    Set objHTTP = CreateObject("MSXML2.XMLHTTP")
    objHTTP.Open "GET", URL, False
    objHTTP.Send
    
    'Get the JSon table
    
    Set RetVal = MyScript.Eval("(" & objHTTP.responseText & ")")
    objHTTP.abort
    
    'Retrieve the value of the key "close" in the 4th item of the data set "Data"
    'with the help of the JScript function "getValue" above
    
    myData = MyScript.Run("getValue", RetVal, 4, "close")
    MsgBox "This is a small demo...." & vbCrLf & vbCrLf _
    & "Value of the key 'close' of the 4th data in the JSON table is: " & myData
    
    'Get the count of items in the JSon table under "Data"
    
    myLength = MyScript.Run("getLength", RetVal)
    
    'Write labels of the key in the table to the sheet
    
    Range("B1") = "time"
    Range("C1") = "close"
    Range("D1") = "high"
    Range("E1") = "low"
    Range("F1") = "open"
    Range("G1") = "volumefrom"
    Range("H1") = "volumeto"
    Range("J1") = "TimeFrom:"
    Range("J2") = "TimeTo:"
    Range("B1:H1, J1:J2").Font.Bold = True
    Range("B1:H1, J1:J2").Font.Color = vbRed
    
    'Get all the values of the JSon table under "Data"
    
    For i = 0 To myLength - 1
        Range("A" & i + 2) = "Data -" & i
        Range("B" & i + 2) = MyScript.Run("getValue", RetVal, i, "time") / (CDbl(60) * CDbl(60) * CDbl(24)) + #1/1/1970#
        Range("C" & i + 2) = MyScript.Run("getValue", RetVal, i, "close")
        Range("D" & i + 2) = MyScript.Run("getValue", RetVal, i, "high")
        Range("E" & i + 2) = MyScript.Run("getValue", RetVal, i, "low")
        Range("F" & i + 2) = MyScript.Run("getValue", RetVal, i, "open")
        Range("G" & i + 2) = MyScript.Run("getValue", RetVal, i, "volumefrom")
        Range("H" & i + 2) = MyScript.Run("getValue", RetVal, i, "volumeto")
    Next
    
    'Get the time info given in the JSon table
    
    Range("K1") = RetVal.TimeFrom / (CDbl(60) * CDbl(60) * CDbl(24)) + #1/1/1970#
    Range("K2") = RetVal.TimeTo / (CDbl(60) * CDbl(60) * CDbl(24)) + #1/1/1970#
    
    Set objHTTP = Nothing
    Set MyScript = Nothing
End Sub

what can you give example of the output that you need to achieve — Akami Kurai
– Akami Kurai, Commented Dec 12, 2017 at 3:55
Hi, I just added two screenshots. Basically, i need the URL to be read from Sheet2 range instead of defining it in my code. In reality, there will be many more than just 3 URLs. imgur.com/a/fEHzt — Yousuf
– Yousuf, Commented Dec 12, 2017 at 4:01
so basically you just need to create a list of hyperlink from the data on Sheet 1 to Sheet 2? but I don't get how can we create the URL String can you provide the formula? and I'll help you create the VBA Loop — Akami Kurai
– Akami Kurai, Commented Dec 12, 2017 at 4:11
No, Sheet1 will be the output of my code. Sheet2 will contain a range of URLs that my code will read in order to find the data and import into Sheet1. Everytime finishes one cell, it will move on to the next in order to get the data from the next URL. — Yousuf
– Yousuf, Commented Dec 12, 2017 at 4:12
you already have a very good example of a for/next loop in the code that you posted — jsotola
– jsotola, Commented Dec 12, 2017 at 4:49

Akami Kurai · Accepted Answer · 2017-12-12 04:21:21Z

1

put everything except these lines on a loop

Dim objHTTP As Object
    Dim MyScript As Object
    Dim myData As Variant
    Dim myLength As Byte

    'Clean the sheet

    ActiveSheet.Cells.Clear

for x=1 to Application.Counta(Sheet2.Columns(1))
...the rest of the code
next

change URL line to URL=Sheet2.Cells(x,1)

and Range to Sheet1.Range

answered Dec 12, 2017 at 4:21

Akami Kurai

1529 bronze badges

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

3 Comments

Yousuf Over a year ago

I think its working but now its giving me an overflow error. This line is causing it: myLength = MyScript.Run("getLength", RetVal)

Yousuf Over a year ago

Hey, i got it to work. I change the DIM mylength from Byte to Integer. I just noticed that by looping this, it keeps overwriting the previous data set that was obtained. How would i work around this?

Akami Kurai Over a year ago

Sorry dude I was also programming my Wen Portal today, anyways declare a Dim RowCur as Long (Note always use long on excel instead of integer) then above the for loop write this "RowCur =2" and change the Range("A1") to Range("A" & Rowcur)

Collectives™ on Stack Overflow

VBA Macro For next loop

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related