(Solved) – VBA Loop on Error Resume Next – How to keep the error line blank or zero


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

BDO 158.00

ABS 15.80

GREEN 1.87

ALHI 1.87

LAND 1.87

PLC 0.57

LBC 0.57

EVER 0.57

Desired Result – the italicized symbols those that are in error will result or will give return of 0

Stock Symbol Price

BDO 158.00

ABS 15.80

GREEN 1.87



PLC 0.57



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")

'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"

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *