Runtime error 9 "subscript out of range"

[eLakmal]

Hi Guys,

I'm kindly new to excel vba, and just learn how to use it this weekend because I need to use it to one of my client.

in my program, I need to export the data from third party software (scada software) to an excel.

it was working for a few line, but then an runtime error 9 "subscript out of range" occur. And from my reading, this error caused by "some object you tried to access by name cannot be found".

when i debug, the error was in this line:

NextRow = Cells(Workbooks("Test.xlsx").Worksheets("Sheet1").Rows.Count, 1).End(xlUp).Row + 1

and this is my full script:

Please Login or Register  to view this content.

Could someone please guide me?

[arlu1201]

A subscript out of range error occurs when the sheet name or workbook name does not match with the sheet name or workbook name in your code.

Check if you have a workbook named Test.xlsx with a worksheet named Sheet1. If your file is named differently or your sheet is named differently, change this line in the code to reflect the same.

[coreytroy]

If you have Macros in your workbook, then chances are the file is Test.xlsm not Test.xlsx

[eLakmal]

Hi Guys,

Thank you for replied, I named it correctly for the workbook and worksheet and there is no macro in my workbook.

[arlu1201]

And you are still getting the error?

[Norie]

For some reason you are creating a new instance of Excel.

It's this instance you are opening the workbook 'Test.xlsx' in.

In the line of code you get the error on you don't reference this instance when trying to refer to the workbook.

This means that VBA will be looking for the workbook in the instance of Excel the code is in.

Either add a reference to the instance you've created or, probably a better idea, don't create a new instance in the first place.

[Norie]

Just realised the code might not be in Excel VBA.

If that's the case try this.

Please Login or Register  to view this content.

PS I think what I said in the previous post still stands.

[eLakmal]

Hello Guys,

Thanks to Norie, I solved the problem.
Norie, I'm quite new to vba so could you please explain to me why your code work and mine is not?
because i guess, it quite the same.

Akmal