In the following macro, the error correction will catch a hyperlink error the first time as an excel error, but the second time, it generates a http error of 4.04 (site not found). Is there a way of catching this error every time?
The line which generates the error is in bold/red.
The macro goes through a list of stock symbols, accesses the website based on the symbol and downloads the history of the stock. But when the symbol does not exsist, an error is generated.
What I need is for when the error occurs, for the macro to jump down and delete the row, that way it can process the next symbol. When there are no errors, this macro works just fine.
Dim x As Integer
Dim cellvalue1
'Dim cellvalue1 As String
Dim myFilename As String
Dim StartMonth As String
Dim StartDay As String
Dim StartYear As String
Dim EndMonth As String
Dim EndDay As String
Dim EndYear As String
StartMonth = Worksheets("NYSEMasterList").Range("AC2").Value
StartDay = Worksheets("NYSEMasterList").Range("AC3").Value
StartYear = Worksheets("NYSEMasterList").Range("AC4").Value
EndMonth = Worksheets("NYSEMasterList").Range("AC6").Value
EndDay = Worksheets("NYSEMasterList").Range("AC7").Value
EndYear = Worksheets("NYSEMasterList").Range("AC8").Value
For x = 1 To 26
Sheets("Insurt").Select
Range("A1").Select
Do While IsEmpty(Range("A1")) = False
cellvalue1 = Range("A1")
Range("C1").Select
ActiveSheet.Hyperlinks.Add anchor:=Selection, Address:= _
"http://ichart.finance.yahoo.com/table.csv?s=" & _
cellvalue1 & "&a=" & StartMonth & "&b=" & StartDay & "&c=" & StartYear & "&d=" & EndMonth & "&e=" _
& EndDay & "&f=" & EndYear & "&g=d&ignore=.csv", _
TextToDisplay:="http://ichart.finance.yahoo.com/table.csv?s=" & _
cellvalue1 & "&a=" & StartMonth & "&b=" & StartDay & "&c=" & StartYear & "&d=" & EndMonth & "&e=" _
& EndDay & "&f=" & EndYear & "&g=d&ignore=.csv"
Range("A1").Select
Selection.Copy
Range("C1").Select
On Error GoTo frys
Selection.Hyperlinks(1).Follow NewWindow:=False, addhistory:=True
On Error GoTo 0
If Err.Number <> 0 Then GoTo frys
ActiveWindow.Visible = False
Windows("Stocktracker.xlsm").Activate
Sheets("Insurt").Select
Range("A1").Select
Selection.Copy
Windows("table.csv").Visible = True
Range("M1").Select
ActiveSheet.Paste
myFilename = Range("M1")
ActiveWorkbook.SaveAs myFilename, FileFormat:= _
xlNormal, Password:="", writerespassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close
frys:
Err.Clear
'Sheets("Insurt").Select
Rows("1:1").Select
Selection.Delete shift:=xlUp
Range("A1").Select
On Error GoTo 0
Loop
Sheets("NYSEMasterList").Select
Range(Cells(1, x), Cells(400, x)).Select
Selection.Copy
Sheets("Insurt").Select
Range("A1").Select
Selection.Insert shift:=xlDown
Next x
The line which generates the error is in bold/red.
The macro goes through a list of stock symbols, accesses the website based on the symbol and downloads the history of the stock. But when the symbol does not exsist, an error is generated.
What I need is for when the error occurs, for the macro to jump down and delete the row, that way it can process the next symbol. When there are no errors, this macro works just fine.
Dim x As Integer
Dim cellvalue1
'Dim cellvalue1 As String
Dim myFilename As String
Dim StartMonth As String
Dim StartDay As String
Dim StartYear As String
Dim EndMonth As String
Dim EndDay As String
Dim EndYear As String
StartMonth = Worksheets("NYSEMasterList").Range("AC2").Value
StartDay = Worksheets("NYSEMasterList").Range("AC3").Value
StartYear = Worksheets("NYSEMasterList").Range("AC4").Value
EndMonth = Worksheets("NYSEMasterList").Range("AC6").Value
EndDay = Worksheets("NYSEMasterList").Range("AC7").Value
EndYear = Worksheets("NYSEMasterList").Range("AC8").Value
For x = 1 To 26
Sheets("Insurt").Select
Range("A1").Select
Do While IsEmpty(Range("A1")) = False
cellvalue1 = Range("A1")
Range("C1").Select
ActiveSheet.Hyperlinks.Add anchor:=Selection, Address:= _
"http://ichart.finance.yahoo.com/table.csv?s=" & _
cellvalue1 & "&a=" & StartMonth & "&b=" & StartDay & "&c=" & StartYear & "&d=" & EndMonth & "&e=" _
& EndDay & "&f=" & EndYear & "&g=d&ignore=.csv", _
TextToDisplay:="http://ichart.finance.yahoo.com/table.csv?s=" & _
cellvalue1 & "&a=" & StartMonth & "&b=" & StartDay & "&c=" & StartYear & "&d=" & EndMonth & "&e=" _
& EndDay & "&f=" & EndYear & "&g=d&ignore=.csv"
Range("A1").Select
Selection.Copy
Range("C1").Select
On Error GoTo frys
Selection.Hyperlinks(1).Follow NewWindow:=False, addhistory:=True
On Error GoTo 0
If Err.Number <> 0 Then GoTo frys
ActiveWindow.Visible = False
Windows("Stocktracker.xlsm").Activate
Sheets("Insurt").Select
Range("A1").Select
Selection.Copy
Windows("table.csv").Visible = True
Range("M1").Select
ActiveSheet.Paste
myFilename = Range("M1")
ActiveWorkbook.SaveAs myFilename, FileFormat:= _
xlNormal, Password:="", writerespassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close
frys:
Err.Clear
'Sheets("Insurt").Select
Rows("1:1").Select
Selection.Delete shift:=xlUp
Range("A1").Select
On Error GoTo 0
Loop
Sheets("NYSEMasterList").Select
Range(Cells(1, x), Cells(400, x)).Select
Selection.Copy
Sheets("Insurt").Select
Range("A1").Select
Selection.Insert shift:=xlDown
Next x