PDA

View Full Version : Solved: Runtime Error 9 - Subscript Out of Range - I must be missing something...



NateW
10-19-2007, 01:07 PM
I am constantly getting "Runtime Error 9 - Subscript Out of Range" with the following code, and it's causing me to lose my mind. I have checked all the references, but I must be missing something... I'm hoping that someone with fresh eyes can check this out and see where I'm going wrong....

Please see the following code:


Sub PopulateEDM_LOADS()

Dim EDM_LOADS_Row, EDMEXPORT_Row As Integer


Application.ScreenUpdating = False

EDM_LOADS_Row = 1
EDMEXPORT_Row = 1

Sheets("EDM_LOADS").Activate



Do Until Workbooks("EDMEXPORT").Sheets("Data").Cells(EDMEXPORT_Row, 1).Value = ""
Cells(EDM_LOADS_Row, 1).Value = Workbooks("EDMEXPORT").Sheets("Data").Cells(EDMEXPORT_Row, 1).Value 'Order Number
Cells(EDM_LOADS_Row, 2).Value = Workbooks("EDMEXPORT").Sheets("Data").Cells(EDMEXPORT_Row, 2).Value 'Status
Cells(EDM_LOADS_Row, 3).Value = Workbooks("EDMEXPORT").Sheets("Data").Cells(EDMEXPORT_Row, 3).Value 'Shipping Method
Cells(EDM_LOADS_Row, 4).Value = Workbooks("EDMEXPORT").Sheets("Data").Cells(EDMEXPORT_Row, 4).Value 'Shipping Instructions
Cells(EDM_LOADS_Row, 5).Value = Workbooks("EDMEXPORT").Sheets("Data").Cells(EDMEXPORT_Row, 5).Value 'Order Type
Cells(EDM_LOADS_Row, 6).Value = Workbooks("EDMEXPORT").Sheets("Data").Cells(EDMEXPORT_Row, 6).Value 'Request Date
Cells(EDM_LOADS_Row, 7).Value = Workbooks("EDMEXPORT").Sheets("Data").Cells(EDMEXPORT_Row, 7).Value 'Ship To
Cells(EDM_LOADS_Row, 8).Value = Mid(Workbooks("EDMEXPORT").Sheets("Data").Cells(EDMEXPORT_Row, 8).Value, 1, 8) 'Pool Number



EDM_LOADS_Row = EDM_LOADS_Row + 1
EDMEXPORT_Row = EDMEXPORT_Row + 1

Loop

Application.ScreenUpdating = True

End Sub




Again, I've gone over this a ton of times, but I'm getting nowhere...any suggestions? Thanks so much in advance for any help...

NateW
10-19-2007, 01:09 PM
When debugging, the first line of the loop gets highlighted...

jtrowbridge
10-19-2007, 01:11 PM
Two questions:

A. What line is the error popping up on?

B. What's your spreadsheet look like?

side note: you dont need to do cells(x,y).value Cells(x,y) works too. Its just a preference though.

NateW
10-19-2007, 01:13 PM
The target worksheet is a blank worksheet. The data worksheet is just raw data exported from an Oracle DB.... Nothing special, just data.

jtrowbridge
10-19-2007, 01:19 PM
If your not switching between workbooks drop the workbook reference to simplify things. This may help if both the tabs are in the "EDMEXPORT" workbook. The physical structure of your loop looks ok to me.


Id try this and see what happens:

Sub PopulateEDM_LOADS()

Dim EDM_LOADS_Row, EDMEXPORT_Row As Integer


Application.ScreenUpdating = False

EDM_LOADS_Row = 1
EDMEXPORT_Row = 1

Sheets("EDM_LOADS").Activate



Do Until Sheets("Data").Cells(EDMEXPORT_Row, 1).Value = ""
Cells(EDM_LOADS_Row, 1).Value = Sheets("Data").Cells(EDMEXPORT_Row, 1).Value 'Order Number
Cells(EDM_LOADS_Row, 2).Value = Sheets("Data").Cells(EDMEXPORT_Row, 2).Value 'Status
Cells(EDM_LOADS_Row, 3).Value = Sheets("Data").Cells(EDMEXPORT_Row, 3).Value 'Shipping Method
Cells(EDM_LOADS_Row, 4).Value = Sheets("Data").Cells(EDMEXPORT_Row, 4).Value 'Shipping Instructions
Cells(EDM_LOADS_Row, 5).Value = Sheets("Data").Cells(EDMEXPORT_Row, 5).Value 'Order Type
Cells(EDM_LOADS_Row, 6).Value = Sheets("Data").Cells(EDMEXPORT_Row, 6).Value 'Request Date
Cells(EDM_LOADS_Row, 7).Value = Sheets("Data").Cells(EDMEXPORT_Row, 7).Value 'Ship To
Cells(EDM_LOADS_Row, 8).Value = Mid(Sheets("Data").Cells(EDMEXPORT_Row, 8).Value, 1, 8) 'Pool Number



EDM_LOADS_Row = EDM_LOADS_Row + 1
EDMEXPORT_Row = EDMEXPORT_Row + 1

Loop

Application.ScreenUpdating = True

End Sub

NateW
10-19-2007, 01:21 PM
Sorry, to clarify...it is two different workbooks... The data is coming from a seperate workbook generated by the Oracle query, and is being copied into the target worksheet, which is part of a larger workbook...

jtrowbridge
10-19-2007, 01:22 PM
If you want to specifically reference the workbook you need to tack on the file extension to the file name.

"EDMEXPORT" would become "EDMEXPORT.xls" or "EDMEXPORT.csv" or whatever.

I just noticed that after the last post I made, thats your problem.

NateW
10-19-2007, 01:22 PM
This module is part of the target workbook.

jtrowbridge
10-19-2007, 01:23 PM
Ah, you need to reference both workbooks then.

Workbooks("FILENAME_A.xls").Cells(EDM_LOADS_Row, 1).Value = Workbooks("FILENAME_B.xls").Sheets("Data").Cells(EDMEXPORT_Row, 1).Value 'Order Number

NateW
10-19-2007, 01:25 PM
Ah! Perfect, did what it was supposed to! Thanks for the help and the fresh eyes...been banging my head against this one for some time.

Must be Friday afternoon...hehe...

Thanks again!!

NateW
10-19-2007, 01:26 PM
Actually, did it fine without referencing the target workbook...

jtrowbridge
10-19-2007, 01:26 PM
No prob. I didnt notice the problem at first. I hate it when I wrestle around all day with stuff like that.

jtrowbridge
10-19-2007, 01:28 PM
Yeah, its relative. Since you were already in the target workbook you didnt need to specify it. I was just being conservative so that it would work no matter what workbook you activated.

have a good one! its kind of wired doing a real time thread, we're pretty much typing at the same time! haha

NateW
10-19-2007, 01:30 PM
haha...yeah, no doubt. I can fire off the replies pretty quickly, as this had me stalled for a good hour and a half, two hours. My attention was all yours!!

Thanks again - have a great weekend.

Cheers!