hyperlink error in macro

stevedc1

New Member
Joined
May 19, 2011
Messages
1
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Forum statistics

Threads
1,224,586
Messages
6,179,730
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top