Run-time error '9' on With statement
 

I wrote some code to sort through a huge dataset in one workbook, find a
particular piece of data and insert it into another workbook. The code is
contained in a module in the latter workbook. The code worked fine, but I
tried to adapt it to do something similar in another module and started
getting a run time error. So I tried executing the original code and I'm
getting the same error now with the original code. How could the code have
worked and now it doesn't if I didn't change anything? The line(s) of code in
question is:

With Workbooks("Workbook_Name").Sheets("Sheet_Name").Ra nge"Named_Range")
'<<THIS LINE IS WHERE I GET THE ERROR
Set rngVariable = .Find(what:=strVariable, LookIn:=xlValues)
End With

If I activate the workbook I'm looking in I think it will fix this problem,
but I wanted to avoid this because it will cause the code to switch back and
forth between workboks again and again (the code is imbedded in a loop). Any
insight into the cause/solution would be much appreciated!

Thanks in advance.

Run-time error '9' on With statement
 

Hello Charlie,

Error 9 is "Subcript out of Range". Something I did notice in your code
which might be a typo is a mssing "(" after Range.

Posted...

With Workbooks("Workbook_Name").Sheets("Sheet_Name").Ra
nge"Named_Range")

Should be...

With Workbooks("Workbook_Name").Sheets("Sheet_Name").Ra
nge("Named_Range")

That would flag a different error if your code were typed this way. If
the Sheet isn't active when the code is run, you will get error 9. Also
check that your variables contain valid data.

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=496442

Run-time error '9' on With statement
 

Try it this way
With Workbooks("Workbook_Name.xls").Range("Named_Range" ).RefersToRange
.find . . .

--
Regards,
Tom Ogilvy



"xlcharlie" wrote in message
...
I wrote some code to sort through a huge dataset in one workbook, find a
particular piece of data and insert it into another workbook. The code is
contained in a module in the latter workbook. The code worked fine, but I
tried to adapt it to do something similar in another module and started
getting a run time error. So I tried executing the original code and I'm
getting the same error now with the original code. How could the code
have
worked and now it doesn't if I didn't change anything? The line(s) of code
in
question is:

With
Workbooks("Workbook_Name").Sheets("Sheet_Name").Ra nge"Named_Range")
'<<THIS LINE IS WHERE I GET THE ERROR
Set rngVariable = .Find(what:=strVariable, LookIn:=xlValues)
End With

If I activate the workbook I'm looking in I think it will fix this
problem,
but I wanted to avoid this because it will cause the code to switch back
and
forth between workboks again and again (the code is imbedded in a loop).
Any
insight into the cause/solution would be much appreciated!

Thanks in advance.

Run-time error '9' on With statement
 

With
Workbooks("Workbook_Name.xls").Range("Named_Range" ).RefersToRange

I think you meant

With
Workbooks("Workbook_Name.xls").Names("Named_Range" ).RefersToRange


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Tom Ogilvy" wrote in message
...
Try it this way
With
Workbooks("Workbook_Name.xls").Range("Named_Range" ).RefersToRange
.find . . .

--
Regards,
Tom Ogilvy



"xlcharlie" wrote in
message
...
I wrote some code to sort through a huge dataset in one
workbook, find a
particular piece of data and insert it into another workbook.
The code is
contained in a module in the latter workbook. The code worked
fine, but I
tried to adapt it to do something similar in another module
and started
getting a run time error. So I tried executing the original
code and I'm
getting the same error now with the original code. How could
the code
have
worked and now it doesn't if I didn't change anything? The
line(s) of code
in
question is:

With
Workbooks("Workbook_Name").Sheets("Sheet_Name").Ra nge"Named_Range")
'<<THIS LINE IS WHERE I GET THE ERROR
Set rngVariable = .Find(what:=strVariable,
LookIn:=xlValues)
End With

If I activate the workbook I'm looking in I think it will fix
this
problem,
but I wanted to avoid this because it will cause the code to
switch back
and
forth between workboks again and again (the code is imbedded
in a loop).
Any
insight into the cause/solution would be much appreciated!

Thanks in advance.

Run-time error '9' on With statement
 

yes, I was waiting for it to show up so I could post the correction.
Accidentally Hit the send key before I finished editing. Thanks.

--
Regards,
Tom Ogilvy



"Chip Pearson" wrote in message
...
With
Workbooks("Workbook_Name.xls").Range("Named_Range" ).RefersToRange

I think you meant

With
Workbooks("Workbook_Name.xls").Names("Named_Range" ).RefersToRange


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Tom Ogilvy" wrote in message
...
Try it this way
With
Workbooks("Workbook_Name.xls").Range("Named_Range" ).RefersToRange
.find . . .

--
Regards,
Tom Ogilvy



"xlcharlie" wrote in
message
...
I wrote some code to sort through a huge dataset in one
workbook, find a
particular piece of data and insert it into another workbook.
The code is
contained in a module in the latter workbook. The code worked
fine, but I
tried to adapt it to do something similar in another module
and started
getting a run time error. So I tried executing the original
code and I'm
getting the same error now with the original code. How could
the code
have
worked and now it doesn't if I didn't change anything? The
line(s) of code
in
question is:

With
Workbooks("Workbook_Name").Sheets("Sheet_Name").Ra nge"Named_Range")
'<<THIS LINE IS WHERE I GET THE ERROR
Set rngVariable = .Find(what:=strVariable,
LookIn:=xlValues)
End With

If I activate the workbook I'm looking in I think it will fix
this
problem,
but I wanted to avoid this because it will cause the code to
switch back
and
forth between workboks again and again (the code is imbedded
in a loop).
Any
insight into the cause/solution would be much appreciated!

Thanks in advance.