Create an alert when attempting to import invalid data

[Monsignor]

My code imports stock historical data. Help would be appreciated in creating a way for a user know that they chose an invalid date when trying to import stock information from the internet.

In cases where a user inputs a date before the stock existed, the current code returns blank fields. It'd be nice to have a simple alert. Even if it just populates with "INVALID DATE. TRY AGAIN."

Maybe this is where the code needs to be modified:

Set qryTableStocks = ThisWorkbook.Worksheets("Web Query Page").QueryTables(1)

There won't be a Table 1 if the stock didn't exist. Instead, Yahoo posts:
"Historical data not available for selected date range."

Maybe use an IF statement that first checks to see if there is a Table 1?

All help is appreciated.

Here's the full code

Sub LoopHistory_Oz()

' Local Variables
Dim IE As InternetExplorer
Dim HTMLDoc As HTMLDocument

Dim rngTicker As Range
Dim rngStMo As Range
Dim rngStDa As Range
Dim rngStYr As Range

Dim rngQuerySym As Range
Dim rngQuerySymCo As Range

Dim SS As Worksheet
Dim WQS As Worksheet

Dim rngEMo As Range
Dim rngEDa As Range
Dim rngEYr As Range
Dim rngQuerySymData As Range
Dim qryTableStocks As QueryTable

Set SS = Worksheets("Sell")
Set WQS = Worksheets("Web Query Page")

'Unprotect Sheets
    SS.Unprotect
    WQS.Unprotect
    

Set rngQuerySym = Worksheets("Web Query Page").Range("A1")
Set rngQuerySymCo = Worksheets("Web Query Page").Range("B7")
' Set rngQuerySymData = Worksheets("Web Query Page").Range("A5").Range("E1:P1")
Set qryTableStocks = ThisWorkbook.Worksheets("Web Query Page").QueryTables(1)

Set rngTicker = Worksheets("Sell").Range("A2")
Set rngStMo = Worksheets("Sell").Range("M2")
Set rngStDa = Worksheets("Sell").Range("N2")
Set rngStYr = Worksheets("Sell").Range("O2")
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False
With ActiveWorkbook.Sheets("Sell")
    ' Dim LastCol As Long
    ' LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    ' Dim NewCol As Long: NewCol = LastCol + 1

'   Step 1 : Set Data Ranges for History Data
Set rngEMo = Worksheets("Sell").Range("Q2")
Set rngEDa = Worksheets("Sell").Range("R2")
Set rngEYr = Worksheets("Sell").Range("S2")
Set rngQuerySymData = Worksheets("Web Query Page").Range("A3").Range("A1:G1")
Set qryTableStocks = ThisWorkbook.Worksheets("Web Query Page").QueryTables(1)


    On Error Resume Next
'   Step 2 : Loop through list of stocks and retrieve Historical Data
Do While rngTicker <> ""
        rngQuerySym = rngTicker
'   Clear old contents
    WQS.Range("A2:I20").ClearContents


' Must subtract 1 from the month because the Yahoo URL goes Jan = 00 thru Dec = 11

With qryTableStocks
    .Connection = "URL;http://finance.yahoo.com/q/hp?s=" & rngTicker & "&a=" & (rngStMo - 1) & "&b=" & rngStDa & "&c=" & rngStYr & "&d=" & (rngEMo - 1) & "&e=" & rngEDa & "&f=" & rngEYr & "&g=m"

         .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingAll
         .WebTables = "15"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
End With
    rngTicker.Range("T2") = rngQuerySymCo
    rngTicker.Range("T1:Z1") = rngQuerySymData.Value
    Set rngTicker = rngTicker.Offset(1, 0)
    
    Set rngStMo = rngStMo.Offset(1, 0)
    Set rngStDa = rngStDa.Offset(1, 0)
    Set rngStYr = rngStYr.Offset(1, 0)
    Set rngEMo = rngEMo.Offset(1, 0)
    Set rngEDa = rngEDa.Offset(1, 0)
    Set rngEYr = rngEYr.Offset(1, 0)

Loop

End With
    Range("AD7").Value = Date
    Range("AE7").Value = Time



'Protect Sheets
    SS.Protect
    WQS.Protect

End Sub

 

[Monsignor]

Got it!


I added a piece of code to check is there was any information imported. If not, then "INVALID DATE"

End With
    rngTicker.Range("T2") = rngQuerySymCo
    rngTicker.Range("T1:Z1") = rngQuerySymData.Value
                    If rngQuerySymData.Value = "" Then Range("T2") = "INVALID DATE"

    Set rngTicker = rngTicker.Offset(1, 0)