MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Run time error

  Asked By: Charlie    Date: Nov 29    Category: MS Office    Views: 1706

I need an urgent help. Whenever I want to retrieve
data from a workbook e.g fixed.xls I am getting
runtimeerror 9.

My code goes like this

Dim a as string
x= workbooks(a).Worksheets(1).Cells(i,2).value

the problem is it is not activating the workbook and
giving run time error 9 and oversubscript error..



5 Answers Found

Answer #1    Answered By: Rochelle Elliott     Answered On: Nov 29

Have you got the name "Fixed.xls" absolutely correct? If the sheet
does not exist you get the Run-time error  '9'; Subscript out of range
Does Worksheet(1) exist? (I suppose it must) but to debug try
temporarily referring to it with its name instead of its index eg.:
x = Workbooks("Sheet1").Worksheets(1).Cells(i, 2).Value

The code  in your message doesn't activate Fixed.xls by the way, nor
does it need to, to work.

Answer #2    Answered By: Silvia Chapman     Answered On: Nov 29

Is the workbook  already open? Have you tried activating  the workbook? The code
you have sent only sets the variables - it doesn't do anything with them.

Answer #3    Answered By: Ty Thompson     Answered On: Nov 29

Is the workbook  "fixed.xls" open, when you run  the procedure? In case
not add to the code  lines from help  which opens workbook and close it.

Answer #4    Answered By: Grady Stewart     Answered On: Nov 29

I got solution of the problem  and thought to share
with u all.

My file name was Fixed.xls
and i was giving run  time error  against Fixed.xls
But when I changed my argument to "fixed.xls"....that
is all in small letters...it accepted and my problem
was solve..

Answer #5    Answered By: Brendan Smith     Answered On: Nov 29

Have u opened the work book before storing the value in any variable?
Try following code  for ur problem, and then see wht happen?

Dim a as string
Dim filepath as string
filepath = "C:\"
workbooks.open(filepath & a)
x= workbooks(a).Worksheets(1).Cells(i,2).value

Didn't find what you were looking for? Find more on Run time error Or get search suggestion and latest updates.