View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Dave Peterson[_3_] Dave Peterson[_3_] is offline
external usenet poster
 
Posts: 2,824
Default 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