Logo 
Search:

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: 976
  

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
a="Fixed.xls"
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..

Share: 

 

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
message.
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:\"
a="Fixed.xls"
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.




Tagged: