Runtime error 9, subscript out of range

[jrickards]

I've looked up other posts on this error code but I still can't figure out my problem.

I've reduced my code to just a few lines.

Dim EastingArr As Variant
Dim k as Long
EastingArr = Workbooks("Area Calculations.xlsm").Worksheets("Sheet1").Range("Easting")
k = UBound(EastingArr)
MsgBox (k)
MsgBox (EastingArr(1))

I get the message box with k (18 is the number of elements in the spreadsheet and this is what I get from the message box) but then the error message appears. It appears that I can't access the items in the array.

Excel 2010

 

[JoeMo]

That array has two dimensions (row & column). Try:
MsgBox EastingArr(1,1)

 

[jrickards]

That array has two dimensions (row & column). Try:
MsgBox EastingArr(1,1)

HAH!! That worked except that I don't want a 2-dimensional array, just a single dimension.

 

[JoeMo]

HAH!! That worked except that I don't want a 2-dimensional array, just a single dimension.

Assigning the values in a range to a variant results in a 2-D array even if the range is restricted to a single row or column, but if your range "Easting" is a single row or column you can convert the variant to a 1-D array.

For example if Easting is one column:
Dim oneD()
'code to create EastingArr here
Redim oneD(1 to Ubound(EastingArr,1))
for i = 1 to Ubound(EastingArr,1)
oneD(i) = EastingArr(i,1)
Next i

 

[jrickards]

Assigning the values in a range to a variant results in a 2-D array even if the range is restricted to a single row or column, but if your range "Easting" is a single row or column you can convert the variant to a 1-D array.

OK, I get it, it is like a single column in a spreadsheet. You can't address values in the column simply by the row number, even if there is only one column, you must use the column identifier. I'm fine with that, I don't need to go to the trouble of converting it to a 1D array.

Many thanks,

Jules

 

[JoeMo]

OK, I get it, it is like a single column in a spreadsheet. You can't address values in the column simply by the row number, even if there is only one column, you must use the column identifier. I'm fine with that, I don't need to go to the trouble of converting it to a 1D array.

Many thanks,

Jules

You are welcome - thanks for the reply.