# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  > [SOLVED] Runtime error 9 "subscript out of range"

## MyCousinVinnie

Hello everyone!

Problem: runtime error 9, subscript out of range - everywhere!

Given: Workbooks MEgdF.xls and MEgdB.xls. 
Task: Transfer input data and estimated data from MEgdF.xls to MEgdB.xls.

Workbook MEgdF.xls
Worksheet Data&Parms, Column H: Input data in cells 1 to 21
Worksheet Output, Column F: estimated data in cells 1 to 41.

Workbook MEgdB.xls
Worksheet Data&Parms, Column H: Estimated data from MegdF.xls read into cells 1 to 41
Worksheet Output, Column F: Input data from MegdF.xls read into cells 1 to 41.

I am using Excel 2007 and I know nothing of Excel VBA whatsoever.

Macros tried available upon request.

Can anyone help?

Best regards,

MyCousinVinnie

----------


## JBeaucaire

That error translates into:  _"some object you tried to access by name cannot be found"_.

So, your code tried to access a workbook in a folder it couldn't find.  Or a sheet in a workbook it couldn't find, or something like that.  Spelling always counts when addressing commands to objects.

----------


## MyCousinVinnie

So where is the error??




```
Please Login or Register  to view this content.
```

----------


## JBeaucaire

You're kidding, right?

When you get the error, click DEBUG.  That will highlight the line of code where the failure is occuring.  Now you just have to quadruple check that line of code and all the strings mentioned in that line of code.

----------


## MyCousinVinnie

Not at all! I've gone this route. 

The reason that I posted this thread is that I know nothing of EXCEL VBA and automation is essential. It beats doing it by hand.  The MEgdF.xls code was produced by a local expert sometime in the nineties and he has long since departed.

This Macro transfer code was presented recently by another individual knowledgeable in EXCEL VBA, but it does not work either. Debug shows that one error is at, LenH = wbMEgdF.Worksheets("Data&Parms").Range("B1").Value ' column H of Data&Parms of wbMEgdF, and I suspect that Len F is next.

I am not wedded to any one Macro. All I need is one that solves this problem as stated in my first post.   

Now I can make the above Macro work if I create two "workbooks" named "MEgdF.xls" and "MEgdB.xls" (just spreadsheets) and create worksheets "Data&Parms" and "Output" on each with sheets 2,3 and 4 in between, and with 21 numbers in column H and 41 numbers in column F in "Data&Parms" and "Output" respectively in MEgdF.xls, and then execute. This works perfectly. But when this is tried in the full MEgdF.xls and MEgdB.xls workbooks - NO CIGAR!

----------


## JBeaucaire

*LenH* was declared as a LONG variable, so it can only be a whole number greater than zero.

So the error on that line of code could mean:

1. *wbMEgdF* no longer is defined.  When you debug, hover your mouse over this and see if it pops up a value for *wbMEgdF*

2. Worksheets("*Data&Parms*") can't be found in the wb that IS currently defined in wbMEgdF.  If #1 is ok, check that current wb for a sheet with that exact name (no hidden spaces at the end).

3. The value in Range("B1") cannot legitimately fill the LenH variable.

You might try one of Excel's built in string functions that might successfully convert a "string" value into a long value...




```
Please Login or Register  to view this content.
```

----------


## MyCousinVinnie

JB as you suggested:

I hope that I did this correctly.

I hovered the mouse over LenH and LenF and both read = 0.
Then both rH and rF = Nothing.

I also hope that I am reading your statement 2. correctly but within the program 
I find "Data&Parms" reading everywhere as "Data & Parms". Is this what you are referring to?
If so, should the transfer code be brought into agreement with wbMEgdF.xls and wbMEgdB.xls?

In addition, when I open the VB Editor, I see (Data & Parms) lisrted as Sheet 1. That's OK.
But (Output) is listed as sheet 2 and not as Sheet5 as shown on the spreadsheets. A problem?

Item 3, I am not certain about.

I will try your suggestions and if still no-go, I will try your select code.

Thanks for your prompt and kind response!

Best regards,

MCV

----------


## MyCousinVinnie

Hey JB you did it!!

I corrected the code to read "Data & Parms" as in the programs and it worked great.

Many-many thanks!!

Yes you are a rocket scientist. I met Robert Goddard's sister and nephew. 
I worked with the latter and he was a very good engineer.
I also met Werner vB but he could not have helped me here.

How do I send my cudos for all to note??

So thanks immensely!

MyCousinVinnie and Co.

----------


## JBeaucaire

Glad you found the discrepancy.  VBA is unforgiving of object names, and spaces are a pain in the patootie, eh?   Good job.

----------

