Runtime error '9' without an array in sub

[Jedrious]

I have a macro that is suppossed to import saved data from one xlsm file to another

when running the macro I get
Run-time error '9':
Subscript out of range

when hitting debug on the dialog
RowBounds = Workbooks(OldSheet).Worksheets("Saved Characters").[a1].Value
is highlighted

For reference Worksheets("Saved Characters").[a1] is a CountA function

Sub Import()
Dim OldSheet As String
Dim RowCount As Long
Dim ColumnCount As Long
Dim RowBounds As Long
OldSheet = Application.GetOpenFilename("Character Sheet Files (*.xlsm), *.txt")
RowBounds = Workbooks(OldSheet).Worksheets("Saved Characters").[a1].Value
For RowCount = 2 To RowBounds
For ColumnCount = 1 To 329
ThisWorkbook.Worksheets("Saved Characters").Cells(RowCount + ThisWorkbook.Worksheets("Saved Characters").[a1].Value, ColumCount) = Workbooks(OldSheet).Worksheets("Saved Characters").Cells(RowCount, ColumCount)
Next
Next
End Sub

When hitting help to discover the nature of the error message it tells me that I have exceeded the bounds of an array or have not specified the number of elements in the array

However I haven't created an array

 

[Pelvis_Breaker]

Offensive content removed. Poster has been warned. Further transgressions will be rigorously dealt with.

 

[xenou]

I think the getopenfilename method returns a path:
C:\MyBook.xls

You want a workbook name:
MyBook.xls

Maybe:

OldSheet = Application.GetOpenFilename("Character Sheet Files (*.xlsm), *.txt")
If OldSheet = "" Then
     Exit Sub
Else
     OldSheet = StrReverse(Left(StrReverse(OldSheet), InStr(1, StrReverse(OldSheet), "\") - 1))
End If

Sometimes the error messages are more like guesses...

 

[Jedrious]

The reason is that your version of Microsoft Office is illegal. You must have obtained your version illegally.

Which is odd because it's purchased at Office Depot and I've never had any problems with Validation

 

[mikerickson]

The reason is that GetOpenFileName returns the path to a file, not a file name.

Workbooks(name) must be an open workbook. The name does not include the file path.

Subscript out of Range is the error that is raised when refering to a non-existant member of a collection. Workbooks(as in your case), WorkSheets, Shapes, Collections and others can all throw that error.

 

[xenou]

I guess I'm assuming you will have the workbook open...

Maybe another workaround is to actually open the other workbook, hide it, get the value, then close it...

HTH - I'm a little foggy on getting values from closed workbooks right now...you could also create a link to it on your sheet.

 

[Jedrious]

I think the getopenfilename method returns a path:
C:\MyBook.xls

You want a workbook name:
MyBook.xls

Maybe:

OldSheet = Application.GetOpenFilename("Character Sheet Files (*.xlsm), *.txt")
If OldSheet = "" Then
     Exit Sub
Else
     OldSheet = StrReverse(Left(StrReverse(OldSheet), InStr(1, StrReverse(OldSheet), "\") - 1))
End If

Sometimes the error messages are more like guesses...

The reason is that GetOpenFileName returns the path to a file, not a file name.

Workbooks(name) must be an open workbook. The name does not include the file path.

Subscript out of Range is the error that is raised when refering to a non-existant member of a collection. Workbooks(as in your case), WorkSheets, Shapes, Collections and others can all throw that error.

ok, using a combination of both of your advices(I also tried them individually) I now have the following code

Sub Import()
Dim OldSheet As Variant
Dim RowCount As Long
Dim ColumnCount As Long
Dim RowBounds As Long
OldSheet = Application.GetOpenFilename("Character Sheet Files (*.xlsm), *.txt")
Workbooks.Open Filename:=OldSheet, ReadOnly:=True
If OldSheet = "" Then
     Exit Sub
Else
     OldSheet = StrReverse(Left(StrReverse(OldSheet), InStr(1, StrReverse(OldSheet), "\") - 1))
End If
RowBounds = Workbooks(OldSheet).Worksheets("Saved Characters").[a1].Value
For RowCount = 2 To RowBounds
For ColumnCount = 1 To 329
ThisWorkbook.Worksheets("Saved Characters").Cells(RowCount + ThisWorkbook.Worksheets("Saved Characters").[a1].Value, ColumCount) = Workbooks(OldSheet).Worksheets("Saved Characters").Cells(RowCount, ColumCount)
Next
Next
Workbooks(OldSheet).Close
End Sub

I'm now getting a 1004 Application-defined or object-defined error

with
ThisWorkbook.Worksheets("Saved Characters").Cells(RowCount + ThisWorkbook.Worksheets("Saved Characters").[a1].Value, ColumCount) = Workbooks(OldSheet).Worksheets("Saved Characters").Cells(RowCount, ColumCount) being highlighted

Which as far as the help file is concerned is VBA's version of "I don't know"

 

[RoryA]

You have mis-spelled ColumnCount in that line. If you put Option Explicit at the top of your module it will force you to declare all variables you use and will catch spelling errors like this rather than assuming you meant to use a new variable.
HTH

 

[Jedrious]

You have mis-spelled ColumnCount in that line. If you put Option Explicit at the top of your module it will force you to declare all variables you use and will catch spelling errors like this rather than assuming you meant to use a new variable.
HTH

Thank you so much, no more error messages(now there's just a problem with the math but that's minor)