excel vba question

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Many people who use these forums can't open external links (including me) and so by using them, you restrict the possibility of a reply.

If you could post the code and highlight the line(s) that the error occur, you stand a much better chance.
 
Upvote 0
gallen said:
Many people who use these forums can't open external links (including me) and so by using them, you restrict the possibility of a reply.

If you could post the code and highlight the line(s) that the error occur, you stand a much better chance.
Click to expand...


it's ok, i fixed it, just put it at beginning of the code rather than the end... but, i still want to know why it didn't work,,, lol... but the code is just too hard to explain,,, i use a button on the "iex" page and have to put 2 different criteria plus the ticker symbol on the page to get it to work,,,, say if you put F for ford, then in b2 and b3 you could put "quote" and "latestPrice" succinctly... HERE YOU GO!!!

Code: 
Sub paininthebut()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim symbols As String
Dim symbol As String
Dim quotes As String
Dim quoten As String
Dim quotex As String
Dim n As Integer
Dim n2 As Integer
Dim lastrow As Long
Dim lastcolumn As Long
Dim i As Integer
Dim i2 As Integer

Set wb = ActiveWorkbook
Set ws = Sheets("iex")
'ws.Activate
wb.Sheets("morning").Select


lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
lastcolumn = ws.Cells(2, Columns.Count).End(xlToLeft).Column


If lastrow <= 103 Then paininthebut4
If lastrow <= 103 Then Exit Sub

ws.Range("B19:ZZ100000").ClearContents


For i = 4 To 103

    If Cells(i, 1).Value = "" Then GoTo label2
    symbols = symbols & ws.Range("A" & i).Value & ","
    
label2:
Next i

symbols = Left(symbols, Len(symbols) - 1)
'MsgBox symbols


For i2 = 2 To lastcolumn

        If Cells(2, i2).Value <> "" Then quotes = quotes & Cells(2, i2).Value & ","

Next i2
quotes = Left(quotes, Len(quotes) - 1)
'MsgBox quotes



Set myrequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    myrequest.Open "Get", "https://api.iextrading.com/1.0/stock/market/batch?symbols=" & symbols & "&types=" & quotes

    myrequest.Send


        Dim Json As Object
        Dim item As Object
        Set Json = JsonConverter.ParseJson(myrequest.ResponseText)
        
        
        
For n = 4 To 103

    For n2 = 2 To lastcolumn

        symbol = Cells(n, 1).Value
        symbol = UCase(symbol)

        quoten = Cells(2, n2).Value

        If quoten = "" Then quoten = Cells(2, n2).End(xlToLeft).Value

        quotex = Cells(3, n2).Value



        If symbol = "" Then GoTo label



        If quoten = "quote" Then s = Json(symbol)(quoten)(quotex)

        If quoten = "stats" Then s = Json(symbol)(quoten)(quotex)

        If quoten = "financials" Then s = Json(symbol)(quoten)(quoten)(1)(quotex)

        ws.Range(Cells(n, n2), Cells(n, n2)) = s

label:

    Next n2

Next n





    'ws.Columns("B:Z").AutoFit

'    ws.Range("B4:B" & lastrow).HorizontalAlignment = xlGeneral
'    ws.Range("B4:B" & lastrow).NumberFormat = "$#,##0.00"
'
'Application.DisplayAlerts = True
'Application.ScreenUpdating = True

paininthebut2

End Sub


Sub paininthebut2()
Dim rnga As Range
Dim rngb As Range
Dim rngc As Range

Dim a As Integer
Dim b As Integer
Dim i As Double


Dim lastrow As Long
Dim lastcolumn As Long

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column


i = lastrow / 99

'i = Int(i)

i = WorksheetFunction.RoundDown(i, 0)


Dim symbols As String
Dim quotes As String


For a = 1 To i - 1
b = a * 99

'If a = 8 Then Stop

Set rnga = Range(Cells(b + 5, 1), Cells(b + 99 + 4, 1))
Set rngb = Range("b2", Cells(2, lastcolumn))
Set rngc = Range("b3", Cells(3, lastcolumn))

    For Each cella In rnga
        If cella.Value = "" Then GoTo label
        symbols = symbols & cella.Value & ","
        
label:
    Next
        
        symbols = Left(symbols, Len(symbols) - 1)
        
    
    
    For Each cellb In rngb
        quotes = quotes & cellb.Value & ","
    Next
        
        quotes = Left(quotes, Len(quotes) - 1)
      
    
    
Set myrequest = CreateObject("WinHttp.WinHttpRequest.5.1")
myrequest.Open "Get", "https://api.iextrading.com/1.0/stock/market/batch?symbols=" & symbols & "&types=" & quotes

myrequest.Send


    Set Json = JsonConverter.ParseJson(myrequest.ResponseText)
    
    symbols = ""
    quotes = ""
    
    
    For n = b + 5 To b + 99 + 4
    
        For n2 = 2 To lastcolumn
        
            symbol = Cells(n, 1).Value
            symbol = UCase(symbol)
    
            quoten = Cells(2, n2).Value
    
            If quoten = "" Then quoten = cellb.End(xlToLeft).Value
            
            quotex = Cells(3, n2).Value
            
                    If symbol = "" Then GoTo label2
                    'If Json(symbol)(quoten)(quotex) = "" Then GoTo label2
                    
                    
                    If quoten = "quote" Then s = Json(symbol)(quoten)(quotex)
            
                    If quoten = "stats" Then s = Json(symbol)(quoten)(quotex)
            
                    If quoten = "financials" Then s = Json(symbol)(quoten)(quoten)(1)(quotex)
            
                    Cells(n, n2).Value = s
            



label2:
        Next n2
        
    Next n

Next a

paininthebut3


End Sub



Sub paininthebut3()

Dim ws As Worksheet
Set ws = Sheets("iex")

Dim i As Double
Dim i2 As Integer
Dim last As Long
Dim last2 As Long
Dim lastcolumn As Long


last = Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column



i = last / 99
i = Int(i)

Dim symbols2 As String

Dim n As Integer
Dim n2 As Integer




Dim x As Integer
Dim x2 As Integer
Dim z As String


Dim last3 As Integer

last3 = Cells(Rows.Count, 1).End(xlUp).Row



x = i * 99 + 4


    For x2 = x To last3
        If Cells(x, 1).Value = "" Then GoTo label2
        z = z & Cells(x2, 1).Value & ","
        
label2:
    Next x2
    z = Left(z, Len(z) - 1)

    For i2 = 2 To lastcolumn
        If Cells(2, i2).Value <> "" Then quotes = quotes & Cells(2, i2).Value & ","
    Next i2




        Set myrequest = CreateObject("WinHttp.WinHttpRequest.5.1")
        myrequest.Open "Get", "https://api.iextrading.com/1.0/stock/market/batch?symbols=" & z & "&types=" & quotes

        myrequest.Send
        
            Dim Json As Object
            Dim item As Object
            Set Json = JsonConverter.ParseJson(myrequest.ResponseText)


For n = x To last3

    For n2 = 2 To lastcolumn

        symbol = Cells(n, 1).Value
        symbol = UCase(symbol)

        quoten = Cells(2, n2).Value

        If quoten = "" Then quoten = Cells(2, n2).End(xlToLeft).Value

        quotex = Cells(3, n2).Value

        If symbol = "" Then GoTo label

        If quoten = "quote" Then s = Json(symbol)(quoten)(quotex)

        If quoten = "stats" Then s = Json(symbol)(quoten)(quotex)

        If quoten = "financials" Then s = Json(symbol)(quoten)(quoten)(1)(quotex)

        ws.Range(Cells(n, n2), Cells(n, n2)) = s
label:
    Next n2
    
Next n



    'ws.Columns("B:ZZ").AutoFit



If Range("I1").Value = "yes" Then Application.OnTime Now() + TimeValue("00:00:00"), "sort1"
If Range("B1").Value = "yes" Then Application.OnTime Now() + TimeValue("00:00:30"), "Sheet6.paininthebut"

Application.DisplayAlerts = True
Application.ScreenUpdating = True

'wb.Sheets("morning").Select



End Sub



Sub paininthebut4()

Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim symbols As String
Dim symbol As String
Dim quotes As String
Dim quoten As String
Dim quotex As String
Dim n As Integer
Dim n2 As Integer
Dim lastrow As Long
Dim lastcolumn As Long
Dim i As Integer
Dim i2 As Integer

Set wb = ActiveWorkbook
Set ws = Sheets("iex")
ws.Activate

lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
lastcolumn = ws.Cells(2, Columns.Count).End(xlToLeft).Column

ws.Range("B19: ZZ100000 ").ClearContents


For i = 4 To lastrow

    If Cells(i, 1).Value = "" Then GoTo label2
    
    symbols = symbols & ws.Range("A" & i).Value & ","
    
label2:

Next i

symbols = Left(symbols, Len(symbols) - 1)






For i2 = 2 To lastcolumn

        If Cells(2, i2).Value <> "" Then quotes = quotes & Cells(2, i2).Value & ","

Next i2
quotes = Left(quotes, Len(quotes) - 1)



Set myrequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    myrequest.Open "Get", "https://api.iextrading.com/1.0/stock/market/batch?symbols=" & symbols & "&types=" & quotes

    myrequest.Send


        Dim Json As Object
        Dim item As Object
        Set Json = JsonConverter.ParseJson(myrequest.ResponseText)

        
For n = 4 To lastrow

    For n2 = 2 To lastcolumn

        symbol = Cells(n, 1).Value
        symbol = UCase(symbol)

        quoten = Cells(2, n2).Value

        If quoten = "" Then quoten = Cells(2, n2).End(xlToLeft).Value

        quotex = Cells(3, n2).Value




        
        If symbol = "" Then GoTo label
        If quoten = "financials" And Json(symbol)("quote")("sector") = "" Then GoTo label
        

        If quoten = "quote" Then s = Json(symbol)(quoten)(quotex)

        If quoten = "stats" Then s = Json(symbol)(quoten)(quotex)

        If quoten = "financials" Then s = Json(symbol)(quoten)(quoten)(1)(quotex)

        ws.Range(Cells(n, n2), Cells(n, n2)) = s

label:

    Next n2
Next n





    'ws.Columns("B:Z").AutoFit

    ws.Range("B4:B" & lastrow).HorizontalAlignment = xlGeneral
    ws.Range("B4:B" & lastrow).NumberFormat = "$#,##0.00"
 
If Range("I1").Value = "yes" Then Application.OnTime Now() + TimeValue("00:00:00"), "sort1"
If Range("B1").Value = "yes" Then Application.OnTime Now() + TimeValue("00:00:15"), "Sheet6.paininthebut"
    

Application.DisplayAlerts = True
Application.ScreenUpdating = True

'wb.Sheets("morning").Select

End Sub
 
Last edited by a moderator:
Upvote 0
i had the line "wb.Sheets("morning").Select" at the end of paininthebut3 and 4,,, it worked with less than 99 tickers, which uses paininthebut4, but didn't with more tickers which uses paininthebut2 and 3...
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
QueryTables bug. If one query fails, all subsequent queries will fail unless restart Excel
Replies
4
Views
454
Micron
M
T
  • Solved
Excel not recognising the data, while format is same
Replies
5
Views
140
Timeless5
T
C
  • Question Question
Creating a Card Flipper using VBA
Replies
0
Views
240
Cquake
C
M
  • Solved
VBA - For Cycle with "On Error" not working on the second iteration
Replies
9
Views
170
Joe4
Joe4
E
  • Solved
Syntax problem with SumProduct and range?
Replies
4
Views
254
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,832
Messages
6,181,234
Members
453,026
Latest member
cknader

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top