Store range of values in an array

  • Thread starter Thread starter Trevor Shuttleworth
  • Start date Start date
T

Trevor Shuttleworth

I would like to save a range of values in an array for later processing.
The values are in cells A1 to N1. I'm using the following code to store the
data but, when I try to loop through the array, I immediately get a Run time
error '9': subscript out of range. The lower bound = 1 and the upper bound =
14 which is what I would expect. What am I doing wrong ?

Option Explicit
Option Base 1

Sub FormatChangeSheet()
Dim iColumn As Long
Dim aColumnWidth As Variant
Dim i As Long

'If ActiveWorkbook.Name = ThisWorkbook.Name Then Exit Sub

With ThisWorkbook.Sheets("Settings")
iColumn = .Range("IV1").End(xlToLeft).Column - 1
aColumnWidth = _
Application.Transpose( _
.Range(.Cells(1, 1), .Cells(1, iColumn)))
End With

For i = LBound(aColumnWidth) To UBound(aColumnWidth)
Debug.Print i & " " & aColumnWidth(i)
Next

End Sub
 
Thanks Norman

that works ... but why ? Is it not a one dimensional array ? Why can't I
say aColumnWidth(1), aColumnWidth(2), aColumnWidth(3), ... etc?

At least now I can get on with what I was trying today ... even if I don't
quite understand why.

Thanks again

Trevor
 
Sub FormatChangeSheet()
Dim iColumn As Long
Dim aColumnWidth As Variant
Dim i As Long

'If ActiveWorkbook.Name = ThisWorkbook.Name Then Exit Sub

With ActiveSheet 'ThisWorkbook.Sheets("Settings")
iColumn = .Range("IV1").End(xlToLeft).Column - 1
aColumnWidth = _
Application.Transpose(Application.Transpose( _
.Range(.Cells(1, 1), .Cells(1, iColumn))))
End With

For i = LBound(aColumnWidth) To UBound(aColumnWidth)
Debug.Print i & " " & aColumnWidth(i)
Next

End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
Hi Trevor,
that works ... but why ? Is it not a one dimensional array ?
Click to expand...

Indeed! Your array is a 2-D array and, therefore, to return a value from the
array, it is necessary to stipulate two coordinates - in a worksheet
analogy, it is necessary to provide the row and column references.
 
Hi Trevor,

Just to add, in his adjacent post, Bob Philipps has used an
additional transpose operation to create a 1-D array and,
therefore, he is able to use a single co-ordinate (or index) to
return values from the array.
 
Bob/Norman

thanks for the clarification and options. Much appreciated.

Regards

Trevor
 
Back
Top