I have coded a program that takes the ticker symbols from one
worksheet(1) [i.e. A1 = ticker1, A2 = ticker2....], gets the pricing
information from Yahoo!Finance and inputs it into another
worksheet(2), runs a calculation based off the pricing information,
and copies and pastes the output to another worksheet(3). When the
calculation is complete the computer clears out the pricing
information from worksheet(2) and moves to the next ticker symbol.

The problem however is that if Yahoo!Finance does NOT have pricing
information for the specified ticker, a small message box pops up that
reads "Could not open "http://ichart.finance.yahoo.com/table.csv?
s= ......." When I click "OK" the program execution returns "Run-time
error '1004': Microsoft Office Excel cannot access the file 'http://
ichart.....' There are serveral possible reasons:....."

I want to know if there is a way to click the pop-up "OK" button for
the "Could not open...." message box. I could write an "On Error"
statement to catch the end/debug window after clicking "OK", but I
don't know how to get passed the "Could not open...." message box. (I
want to run this on several ticker symbols, and it would be a pain to
have to manually click the "OK" button each time this type of
situation occurs).

I have included my code below. A list of tickers that work as
expected are the following: JNJ, ADBE, APOL, LLTC. A list of tickers
that produce the "Could not open...." message box are the following:
RMK, HCA, RUSMF, ITWG.

Any help is appreciated. Thanks in advance. (Also, make sure that
VBE: Tools/References/Microsoft Forms 2.0 Object Library is selected).

Option Explicit
Sub yahooCSV2()
Dim a 'start month
Dim b 'start day
Dim c 'start year
Dim d 'end month
Dim e 'end day
Dim f 'end year
Dim tic 'ticker
Dim mon 'month
Dim dy 'day
Dim yr 'year
Dim wbCSV 'workbook CSV
Dim wbPT 'workbook Pricing Tool
Dim counter 'counter for the loop
Dim outputCounter 'counter for the output
Dim answer 'value of calculation
Dim m 'marker for the loop
Dim myData As DataObject 'to clear the clipboard

Set myData = New DataObject

wbCSV = "table.csv"
wbPT = ActiveWorkbook.Name

Worksheets(1).Select
counter = Range("a1").CurrentRegion.Rows.Count

For m = 1 To counter
tic = Worksheets(1).Range("a" & m).Value

mon = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mmm")
dy = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd")
yr = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "yyyy")

a = monthToNumber(mon)
b = dy
c = yr - 10

d = a
e = b
f = yr

Workbooks.Open Filename:="http://ichart.finance.yahoo.com/table.csv?
s=" _
& tic & "&a=" & a & "&b=" & b & "&c=" & c &
"&d=" _
& d & "&e=" & e & "&f=" & f &
"&g=d&ignore=.csv"

Workbooks(wbPT).Worksheets(2).Range("a1").CurrentR egion.ClearContents
Workbooks(wbCSV).Activate
Range("a1").CurrentRegion.Copy
Workbooks(wbPT).Worksheets(2).Range("a1").PasteSpe cial

myData.SetText ""
myData.PutInClipboard

Workbooks(wbCSV).Close savechanges:=False

Workbooks(wbPT).Activate
outputCounter = Worksheets(3).Range("a1").CurrentRegion.Rows.Count
answer = Worksheets(2).Range("j1").Value
Worksheets(3).Range("a" & outputCounter + 1).Value = tic
Worksheets(3).Range("b" & outputCounter + 1).Value = answer
Next

End Sub

Private Function monthToNumber(ByVal mon)

Select Case mon
Case "Jan"
monthToNumber = Right(100, 2)
Case "Feb"
monthToNumber = Right(101, 2)
Case "Mar"
monthToNumber = Right(102, 2)
Case "Apr"
monthToNumber = Right(103, 2)
Case "May"
monthToNumber = Right(104, 2)
Case "Jun"
monthToNumber = Right(105, 2)
Case "Jul"
monthToNumber = Right(106, 2)
Case "Aug"
monthToNumber = Right(107, 2)
Case "Sep"
monthToNumber = Right(108, 2)
Case "Oct"
monthToNumber = Right(109, 2)
Case "Nov"
monthToNumber = Right(110, 2)
Case "Dec"
monthToNumber = Right(111, 2)
End Select

End Function