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

[NateW]

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:

[VBA]
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


[/VBA]

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]

When debugging, the first line of the loop gets highlighted...

[jtrowbridge]

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]

The target worksheet is a blank worksheet. The data worksheet is just raw data exported from an Oracle DB.... Nothing special, just data.

[jtrowbridge]

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]

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]

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]

This module is part of the target workbook.

[jtrowbridge]

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]

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]

Actually, did it fine without referencing the target workbook...

[jtrowbridge]

No prob. I didnt notice the problem at first. I hate it when I wrestle around all day with stuff like that.

[jtrowbridge]

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]

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!