2

I'm writing a script to find tickers based on stock names. Naturally, sometimes companies are not found the way they are written. The reason for this is the source usually tacks on some unneccesary bit at the end. Im trying to write my script so that it will repeatedly remove words from the end until the ticker is gotten. If the ticker is never gotten, then to Resume Next and select the next row. Is there a way to do this?

Sub TickerLookup()

cell = ActiveCell.Value

10 If Not IsEmpty(ActiveCell.Value) Then
    GoTo GetTicker
   Else
    GoTo 20
End If
Catch:
    For Each c In Selection
        If Not IsEmpty(c.Offset(, -1)) Then
            cell = Left(cell, InStrRev(cell, " ") - 1)
            MsgBox cell
        Else
            MsgBox "Please clear all adjacent cells"
        End If
 Next
 Resume Next
 Selection.Offset(1, 0).Select
 GoTo 10

GetTicker:

    cell = ActiveCell.Value
    'remInc = Replace(cell, "INC", "")
    'remCos = Replace(remInc, "COS", "")
    cmpnyName = cell

    ticker = "http://dev.markitondemand.com/MODApis/Api/v2/Lookup/json?input=" & cmpnyName

    Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
         MyRequest.Open "GET", ticker
         MyRequest.Send
    Dim Json As Object
         Set Json = JsonConverter.ParseJson(MyRequest.ResponseText)


    On Error GoTo Catch
    ActiveCell.Offset(, -1).Value = Json(1)("Symbol")

    Selection.Offset(1, 0).Select
    GoTo 10

20 End Sub
0m3r
  • 12,286
  • 15
  • 35
  • 71
user2537157
  • 63
  • 1
  • 5
  • 2
    `Goto`?? Really? That is soooo eighties... – trincot May 15 '16 at 19:37
  • You just need to test the `Err` object and then act accordingly, although this seems like a bit of an XY problem - I'll bet there's a much better way of just testing your data instead. – SierraOscar May 15 '16 at 20:06

1 Answers1

2

For the issue you describe (loop until error is gone) the solution is:

Do
    On Error Resume Next 'reset Err.obj.
    'do things you want here
    If (BoolExpressionYoWantIsTrue) Then 'the test you mention: the string is OK
        'do what you want
        Exit Do 'quit loop
    End If
Loop Until (Err.Number = 0)
On Error Goto 0 'turn off error sinking

Also, try to code without ever using the "goto" expression. For example, instead of that "Goto 20" you could just add an Exit Sub instead.