Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
runtime error 9
You sure you have 11 worksheets?
that error #9 means subscript out of range and I don't see any other subscripts in that line of code. Also, for later use: dim gFileCell as range dim gFile as string with worksheets(11) 'after you've fixed this! set gFileCell = .Range("A2:A7736").Find(What:=i, _ After:=.cells(.cells.count), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) if gfilecell is nothing then 'error message else gfile = gfilecell.offset(0,1).value end if end with If you don't find what you're looking for, you're code will break--better to check to see if you found what you were looking for. a wrote: I've got some code below that has run fine elsewhere (another sheet with different pathnames), but it is giving me a runtime error now. Basically it is supposed to go get the cell contents (value) that act as a pathname for a web query. The error is happening at the "gFile = Worksheets(11)...." line. The ranges and the cell references are correct and the pathnames actually do return a document when used elsewhere. (The layout of the table with the pathnames that are looked-up is between the double lines at the bottom of the page.) Any ideas as to why this isn't working? Thanks, Paul ----------------------------------------------------------------------------- Sub Get_Data() Dim i As Integer Dim gFile As String For i = 1 To 2 '7735 'this assigns the path from the Worksheets("Sheet11") to gFile (THE FILE TO OPEN) gFile = Worksheets(11).Range("A2:A7736").Find(What:=i, After:=Worksheets(11).Range("A2"), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _ .Offset(0, 1) With ActiveSheet.QueryTables.Add(Connection:=gFile, Destination:=Range("A1")) .Name = "0001084869-03-000011" .FieldNames = True End With Next i End Sub ================================================ Column Number 1 Number GET (Path To file) 2 1 http://www.sec.gov/Archives/edgar/da...-04-000015.txt ================================================ -- Dave Peterson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
runtime error 9
Dave:
Thanks for the answer. No, I did not have 11 sheets in this new workbook. Both names in the workbook for this particular sheet are 'Sheet11', and I didn't realize that that is not what 'Worksheets(11)' means, so when I changed it to Worksheets("Sheet11") the code ran fine. Again, thank you for pointing me in the right direction. Paul "Dave Peterson" wrote: You sure you have 11 worksheets? that error #9 means subscript out of range and I don't see any other subscripts in that line of code. Also, for later use: dim gFileCell as range dim gFile as string with worksheets(11) 'after you've fixed this! set gFileCell = .Range("A2:A7736").Find(What:=i, _ After:=.cells(.cells.count), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) if gfilecell is nothing then 'error message else gfile = gfilecell.offset(0,1).value end if end with If you don't find what you're looking for, you're code will break--better to check to see if you found what you were looking for. a wrote: I've got some code below that has run fine elsewhere (another sheet with different pathnames), but it is giving me a runtime error now. Basically it is supposed to go get the cell contents (value) that act as a pathname for a web query. The error is happening at the "gFile = Worksheets(11)...." line. The ranges and the cell references are correct and the pathnames actually do return a document when used elsewhere. (The layout of the table with the pathnames that are looked-up is between the double lines at the bottom of the page.) Any ideas as to why this isn't working? Thanks, Paul ----------------------------------------------------------------------------- Sub Get_Data() Dim i As Integer Dim gFile As String For i = 1 To 2 '7735 'this assigns the path from the Worksheets("Sheet11") to gFile (THE FILE TO OPEN) gFile = Worksheets(11).Range("A2:A7736").Find(What:=i, After:=Worksheets(11).Range("A2"), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _ .Offset(0, 1) With ActiveSheet.QueryTables.Add(Connection:=gFile, Destination:=Range("A1")) .Name = "0001084869-03-000011" .FieldNames = True End With Next i End Sub ================================================ Column Number 1 Number GET (Path To file) 2 1 http://www.sec.gov/Archives/edgar/da...-04-000015.txt ================================================ -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
runtime error 9
Watch out for that .find stuff, too.
a wrote: Dave: Thanks for the answer. No, I did not have 11 sheets in this new workbook. Both names in the workbook for this particular sheet are 'Sheet11', and I didn't realize that that is not what 'Worksheets(11)' means, so when I changed it to Worksheets("Sheet11") the code ran fine. Again, thank you for pointing me in the right direction. Paul "Dave Peterson" wrote: You sure you have 11 worksheets? that error #9 means subscript out of range and I don't see any other subscripts in that line of code. Also, for later use: dim gFileCell as range dim gFile as string with worksheets(11) 'after you've fixed this! set gFileCell = .Range("A2:A7736").Find(What:=i, _ After:=.cells(.cells.count), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) if gfilecell is nothing then 'error message else gfile = gfilecell.offset(0,1).value end if end with If you don't find what you're looking for, you're code will break--better to check to see if you found what you were looking for. a wrote: I've got some code below that has run fine elsewhere (another sheet with different pathnames), but it is giving me a runtime error now. Basically it is supposed to go get the cell contents (value) that act as a pathname for a web query. The error is happening at the "gFile = Worksheets(11)...." line. The ranges and the cell references are correct and the pathnames actually do return a document when used elsewhere. (The layout of the table with the pathnames that are looked-up is between the double lines at the bottom of the page.) Any ideas as to why this isn't working? Thanks, Paul ----------------------------------------------------------------------------- Sub Get_Data() Dim i As Integer Dim gFile As String For i = 1 To 2 '7735 'this assigns the path from the Worksheets("Sheet11") to gFile (THE FILE TO OPEN) gFile = Worksheets(11).Range("A2:A7736").Find(What:=i, After:=Worksheets(11).Range("A2"), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _ .Offset(0, 1) With ActiveSheet.QueryTables.Add(Connection:=gFile, Destination:=Range("A1")) .Name = "0001084869-03-000011" .FieldNames = True End With Next i End Sub ================================================ Column Number 1 Number GET (Path To file) 2 1 http://www.sec.gov/Archives/edgar/da...-04-000015.txt ================================================ -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
xpath error? Runtime Error 13 type mismatch | Excel Discussion (Misc queries) | |||
xpath error? Runtime Error 13 type mismatch | Excel Discussion (Misc queries) | |||
Runtime error '1004' General ODBC error | New Users to Excel | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming |