I have a vba code that gets the stock price and the vba code is dynamic, it is a loop. Pls. see code below.
There are stock symbols that are not found in this API source. So tendency is it will result to error for this particular stock. Now I’m using On Error Resume Next so that the VBA code will continue on to the next symbol, but my problem is the symbol in error will return a stock price result equivalent to the price of the last stock symbol not in error. What I would like to achieve is to make the result blank or zero for the stock symbols in error. Below is the current result of the code and result that I want to achieve.
Hope someone could help me on this. Thanks in advance.
Current Result – the italicized symbols are those that are in error.
Stock Symbol Price
Desired Result – the italicized symbols those that are in error will result or will give return of 0
Stock Symbol Price
Sub RefreshStockQuote() Application.DisplayAlerts = False Application.ScreenUpdating = False Dim wb As Workbook Dim ws As Worksheet Dim rng As Range Dim symbol As String Dim n As Integer Dim lastrow As Long Set wb = ActiveWorkbook Set ws = Sheets("Sheet1") ws.Activate 'Last row find lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row Set rng = ws.Range("A4:A" & lastrow) 'Clear Prior Prices ws.Range("B4:D" & lastrow).ClearContents n = 4 'Get Symbols list For Each X In rng symbol = X Set myrequest = CreateObject("WinHttp.WinHttpRequest.5.1") myrequest.Open "Get", "http://phisix-api.appspot.com/stocks/" & symbol & ".json" myrequest.Send Dim Json As Object Set Json = JsonConverter.ParseJson(myrequest.ResponseText) i = Json("stock")(1)("price")("amount") ws.Range(Cells(n, 2), Cells(n, 2)) = i n = n 1 On Error Resume Next Next X ws.Columns("B").AutoFit MsgBox ("Stock Quotes Refreshed.") ws.Range("B4:B" & lastrow).HorizontalAlignment = xlGeneral ws.Range("B4:B" & lastrow).NumberFormat = "#,##0.00" Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub