Subscript out of range - help!

Hi, noboffinme, I really appreciate your response and truly thankful if you could make this work.

the download link for the file: https://www.sendspace.com/file/frn43o

the code you should be focusing is in "sheet19 (stocks)" line 135 to 179 under the tab loaddata()

I want to be able to download say a thousands of stocks data instead of hundreds. Thanks.

Originally Posted by noboffinme

Hi geekohi,

This error means that the code cannot locate something it is supposed to work/make changes on.

It could mean a misspelled/missing object such as a Worksheet name just as one example.

Can you post the file you're using and advise the row that the error occurs on?

Thanks

Hi, noboffinme, I really appreciate your response and truly thankful if you could make this work.

the download link for the file: https://www.sendspace.com/file/frn43o

the code you should be focusing is in "sheet19 (stocks)" line 135 to 179 under the tab loaddata()

I want to be able to download say a thousands of stocks data instead of hundreds. Thanks.

Originally Posted by noboffinme

Can you post the file through this site?

Go to 'Go Advanced' >> 'Manage Attachments' >> 'Select File' >> 'Upload File' >> 'Done'

Thanks

yeap, done.

Originally Posted by Norie

Where in the code do you get the error?

the thing I dont quite understand is, I am not getting any errors in my code.

The code does function as expected when say, I insert roughly 200 stock code on column A.

If I insert > 300-400, then it runs and just says that: subscript out of range (without pointing to me, where in the code I get the error)

Im trying to make this stock data loader applicable to say, thousands of stocks.


The line of code used for the loading process (when press on "load button"), its sheet19 (stocks)

Sub loaddata()

    Dim W As Worksheet: Set W = ActiveSheet
    Dim i As Integer
    Dim Last As Integer
    
    Worksheets("Stocks").Activate
    
    Last = W.Range("A6000").End(xlUp).Row
    
    If Last = 1 Then Exit Sub
    Dim Symbols As String
    For i = 2 To Last
        Symbols = Symbols & W.Range("A" & i).Value & "+"
    Next i
    Symbols = Left(Symbols, Len(Symbols) - 1)
    
    Dim URL As String: URL = "http://finance.yahoo.com/d/quotes.csv?s=" & Symbols & "&f=snd1ohgl1v"
    Dim X As New WinHttpRequest

    X.Open "GET", URL, False
    X.send
    
    Dim Resp As String: Resp = X.responseText
    Dim Lines As Variant: Lines = Split(Resp, vbCrLf)
    Dim sLine As String
    Dim Values As Variant
    For i = 0 To UBound(Lines)
        sLine = Lines(i)
        If InStr(sLine, ",") > 0 Then
            Values = Split(sLine, ",")
            W.Cells(i + 2, 2).Value = Replace(Split(Split(sLine, Chr(34) & "," & Chr(34))(1), Chr(34) & ",")(0), Chr(34), "")
            W.Cells(i + 2, 3).Value = Replace(Values(UBound(Values) - 5), Chr(34), "")
            W.Cells(i + 2, 3).NumberFormat = "yyyy-mm-dd;@"
            W.Cells(i + 2, 4).Value = Values(UBound(Values) - 4)
            W.Cells(i + 2, 5).Value = Values(UBound(Values) - 3)
            W.Cells(i + 2, 6).Value = Values(UBound(Values) - 2)
            W.Cells(i + 2, 7).Value = Values(UBound(Values) - 1)
            W.Cells(i + 2, 8).Value = Values(UBound(Values))
        End If
    Next i
    
    W.Cells.Columns.AutoFit
    
End Sub

Im still a newbie to programming using excel, starting to learn.

Everytime I got an error, excel will roughly highlight the line of code in yellow, I will work from there on.

But for this, when I got the error, there is no indication of where which the line got the error.

Thx Norie, I researched further and found that the limit is set at 200 ticker symbols at a time. Not very convenient if I'm trying to load all at once.

Have any suggestion how I can improvise this.

Can I actually scrap data from the official stock data website, such as this one using the same method as before

but grabbing all data at once based on its stock code:

http://www.bursamalaysia.com/market/...tock_code=0010