"On Error GoTo" always has error with or without Yahoo Stock History Download

[Mustang65]

The code works great, but it does lack one MAJOR item... ERROR Checking for Stock Symbols that are mistyped like "MTE" instead of "MET". Clearing the cells and then no data from Yahoo Stock History causes issues with the formulas in columns H"AF. I am trying to stop the process of clearing cells, if there is an error from Yahoo.

Below is the portion of code that is in question.

No matter where I insert "'On Error GoTo StockSymbolEntryError1" code, it goes directly to 'StockSymbolEntryError1: Which is a MsgBox that displays which error msg it is. if I put it before the Yahoo code to down load the Stock history data I still it still sees an error. IF a correct Stock Symbol is entered, it still goes to the error GoTo. Is there a specific error code from Yahoo that could be used? Sorry about all the Remarks in the code as that is how I keep track as to what is going on...

Thanks in advance for your assistance

Don

'-------------------------------------------------------
   [COLOR=#008000] ' Clear all cells in columns A:G formula rows from H5 through AF last row
    '-------------------------------------------------------[/COLOR]
    Worksheets("BackTest").Range("a:g").ClearContents
    With Worksheets("BackTest")
        LastRow1 = .Range("H" & .Rows.Count).End(xlUp).Row
        .Range("H6:af" & LastRow1).Clear
    End With
     [COLOR=#008000]'------------------------------------------------------
     ' Get Yahoo Stock History Data
     '------------------------------------------------------[/COLOR]
    Set SettingsSheet = Workbooks("BackTest From Scratch - 13.xlsm").Worksheets("Setup")
        SYMBOL = SettingsSheet.Range("D5")
        StartDate = SettingsSheet.Range("D6").Value
        EndDate = SettingsSheet.Range("D7").Value
               qurl = "http://ichart.finance.yahoo.com/table.csv?s=" & SYMBOL
        qurl = qurl & "&a=" & Month(StartDate) + 1 & "&b=" & Day(StartDate) & _
            "&c=" & Year(StartDate) - 1 & "&d=" & Month(EndDate) - 1 & "&e=" & _
            Day(EndDate) & "&f=" & Year(EndDate) & "&g=d&q=q&y=0&z=" & _
            SYMBOL & "&x=.csv"
              [COLOR=#008000]  '--------------------------------------------
                ' Check for valid stock symbol
                'On Error GoTo StockSymbolEntryError1
                '--------------------------------------------[/COLOR]
QueryQuote:
            Set QuerySheet = Workbooks("BackTest From Scratch - 13.xlsm").Worksheets("BackTest")
             
             With QuerySheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=QuerySheet.Range("a1"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
               [COLOR=#008000] '--------------------------------------------
                ' Original Code - Error Check and Location
                '  >>>>>>>> On Error Resume Next <<<<<<<<
                '--------------------------------------------
                '--------------------------------------------
                'Check for valid stock symbol
                '   On Error GoTo StockSymbolEntryError2
                '--------------------------------------------[/COLOR]
                .Refresh BackgroundQuery:=False
                .SaveData = True
              [COLOR=#008000] '--------------------------------------------------
               [/COLOR][COLOR=#ff0000]'Should the clearing of the A:G cells be entered here? IF there I no ERROR condition should the H:AF &
               ' LastRow -4 go here?
[/COLOR][COLOR=#008000]               ' --------------------------------------------------[/COLOR]
           
           Sheets("BackTest").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("BackTest").Range("a1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, other:=False
    End With
    
           Sheets("BackTest").Columns("A:ah").AutoFit
 [COLOR=#008000] '---------------------------------------------------------
  'Lastrow and Countrows of new data download
  '---------------------------------------------------------[/COLOR]
    With Worksheets("BackTest")
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        MsgBox "LastRow: " & LastRow
    End With
   [COLOR=#008000]'--------------------------------------------------------
   ' Clear data from H6 through AH & LastRow
   '--------------------------------------------------------
    'With Worksheets("BackTest")
    '.Range("H6:ae" & LastRow).Clear
   'LastRow2 = .Range("H" & .Rows.Count).End(xlUp).Row
        'MsgBox "LastRow2: " & LastRow2
    'End With
   
   '--------------------------------------------------------[/COLOR]
  [COLOR=#008000] ' Drag down formulas from H5 through LastRow -4
   '--------------------------------------------------------[/COLOR]
     With Worksheets("BackTest")
        .Range("h5:af5").AutoFill Destination:=.Range("h5:af" & LastRow - 5)
        'LastRow3 = .Range("H" & .Rows.Count).End(xlUp).Row
        'MsgBox "LastRow3: " & LastRow3
     End With

 

[Mustang65]

OK, let's try this again. I have tried to simplify my post. Here is the (working) VBA code without all the extra code.
To try it, create:
- a Workbook = "BackTest From Scratch - 13 - Source only.xlsm"
- Worksheet = "SetUp"
- Cells =
- SYMBOL = ("D5") [MET]
- StartDate = ("D6") [01/01/11]
- EndDate = ("D7") [12/31/16]
- Worksheet = "BackTest"
- Columns = A:AF [Active Columns on this worksheet]
- Columns = A:G [Yahoo Stock History Data}
- Columns = H:AF [Just enter any characters in A1:Af1 and fill the data to at least row 20]


ISSUE:
If you enter a Stock Symbol that is not an ACTIVE stock symbol all the data in H6:AF & LastRow1 is cleared and when the next correct stock symbol is entered, those columns now have no formulas in them. I have to get out of the workbook and restart it without saving.

Possible fixs:
- Get a valid data download error that can be used to bypass the sections of code that would clear the columns H6:AF & LastRow1. For some reason it always goes to the StockSymbolEntryError1: and processes everything anyway
- Relocate the clearing of H6:AF & LastRow1 to after the download and bypass if there is a Yahoo download error
or
- Check if there is data in A1 after the download and if there is continue with code else display "Stock Symbol not found" error message and exit

Sub DataDownload()
  
    Dim QuerySheet As Worksheet
    Dim SettingsSheet As Worksheet
    Dim EndDate As Date
    Dim StartDate As Date
    Dim SYMBOL As String
    Dim qurl As String
    Dim LastRow1 As Long
    Dim LastRow2 As Long
    Dim LastRow3 As Long
  
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    LastRow1 = 0
    LastRow2 = 0
    LastRow3 = 0
     
    [COLOR=#008000]'-------------------------------------------------------
    ' Clear all cells in columns A:G formula rows from H5 through AF last row
    '-------------------------------------------------------[/COLOR]
    Worksheets("BackTest").Range("a:g").ClearContents
   [COLOR=#008000] '-------------------------------------------------------
    ' Clear formula cells from H5 through AF & lastrow1 (NEED TO RELOCATE TO AFTER THE DATA DOWNLOAD?)
    '-------------------------------------------------------[/COLOR]
    With Worksheets("BackTest")
        LastRow1 = .Range("H" & .Rows.Count).End(xlUp).Row
        .Range("H6:af" & LastRow1).Clear
    End With
    [COLOR=#008000] '------------------------------------------------------
     ' Get Yahoo Stock History Data
     '------------------------------------------------------[/COLOR]
    Set SettingsSheet = Workbooks("BackTest From Scratch - 13 - Source only.xlsm").Worksheets("Setup")
        SYMBOL = SettingsSheet.Range("D5")
        StartDate = SettingsSheet.Range("D6").Value
        EndDate = SettingsSheet.Range("D7").Value
               qurl = "http://ichart.finance.yahoo.com/table.csv?s=" & SYMBOL
        qurl = qurl & "&a=" & Month(StartDate) + 1 & "&b=" & Day(StartDate) & _
            "&c=" & Year(StartDate) - 1 & "&d=" & Month(EndDate) - 1 & "&e=" & _
            Day(EndDate) & "&f=" & Year(EndDate) & "&g=d&q=q&y=0&z=" & _
            SYMBOL & "&x=.csv"
      
QueryQuote:
            Set QuerySheet = Workbooks("BackTest From Scratch - 13 - Source only.xlsm").Worksheets("BackTest")
             
             With QuerySheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=QuerySheet.Range("a1"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
   [COLOR=#008000] '------------------------------------------------------
    ' Original Error Check and Location (I have remarked it out)
    ' On Error Resume Next
    '------------------------------------------------------[/COLOR]
                .Refresh BackgroundQuery:=False
                .SaveData = True
           
           Sheets("BackTest").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("BackTest").Range("a1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, other:=False
             End With
     [COLOR=#008000] '------------------------------------------------------
      ' Here is my "On Error GoTo"
      '------------------------------------------------------[/COLOR]
      On Error GoTo StockSymbolEntryError1
           Sheets("BackTest").Columns("A:ah").AutoFit
        With Worksheets("BackTest")
            LastRow2 = .Range("A" & .Rows.Count).End(xlUp).Row
        End With
   [COLOR=#008000]  '--------------------------------------------------------
     ' Drag down formulas from H5 through AH & LastRow2 -5 (I believe I should be clearing theses cells at this point after it confirms there is no "YAHOO ERROR"
     '--------------------------------------------------------[/COLOR]
     With Worksheets("BackTest")
        .Range("h5:af5").AutoFill Destination:=.Range("h5:af" & LastRow2 - 5)
        LastRow3 = .Range("A" & .Rows.Count).End(xlUp).Row
            MsgBox "LastRow of Column (H) After Fill" & LastRow3
     End With
   
   
StockSymbolEntryError1:
    MsgBox "You have entered an invalid stock symbol (1)"
    MsgBox "Worksheet update completed"
End Sub

 

[Mustang65]

Issue resolved