Problem with macro written for Excel 2003

Mac10

New Member
Joined
Sep 17, 2010
Messages
38
I keep getting "Compile error: Can't find project or library"

Please help.


Code:
Sub RunExtractor()

    GetSector

End Sub

Public Function GetSector()
    ' Start here, get a list of sectors then drill down in the Industry page.
    On Error GoTo ErrTrap
    Dim nRowYahoo As Integer      ' The Yahoo Pg
    Dim nRowExcel As Integer
    Dim sYahooPage As String
    Dim sBaseURL As String
    
    Debug.Print "Sector Started", Now()
    
    sBaseURL = "http://biz.yahoo.com/p/"
    sYahooPage = "s_qtou.html"
    nRowYahoo = 0
    nRowExcel = 2  ' Start at Row 2 instead of 1 so headers can be put in later to show A is the Sector and B is the URL
    
    ' Clear out current area. Leave the header row in place if someone wants it labeled
    Workbooks("Yahoo-Extractor.xls").Worksheets("Sector").Range("A2:Z15000").Value = ""
    ' First open the Yahoo page in a new workbook, that has all the Sectors listed.
    Workbooks.Open sBaseURL & sYahooPage
    
    ' Search the A columns for the text "Click on column heading to sort." to find the place to
    ' start the extraction at. Will have to cursor down to get the first line, currently 4 lines
    With Worksheets(1).Range("A1:A200")
        Set s = .Find("Click on column heading to sort.")
        If Not s Is Nothing Then ' Found
            nRowYahoo = s.Row + 4  ' Add 4 to get to the first item, usually "Basic Materials"
            Do
                ' Get the Sector name and store to the workbook
                Workbooks("Yahoo-Extractor.xls").Worksheets("Sector").Range("A" & nRowExcel) = Range("A" & nRowYahoo).Value
                ' Get the URL and store it to the workbook in the second column
                Workbooks("Yahoo-Extractor.xls").Worksheets("Sector").Range("B" & nRowExcel) = Range("A" & nRowYahoo).Hyperlinks.Item(1).Address
                
                ' Increment the row counters
                nRowYahoo = nRowYahoo + 1
                nRowExcel = nRowExcel + 1

                ' Continue looping until the value in the Yahoo page column 1 is blank, then exit this routine.
            Loop While Range("A" & nRowYahoo).Value <> ""
        End If
    End With
    ' Close Yahoo workbook
    Workbooks(sYahooPage).Close
    Exit Function
    
ErrTrap:
    Debug.Print "GetSector", Err.Description, Err.Number
    Resume Next
End Function
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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