Store range of values in an array

[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

 

[Norman Jones]

Hi Trevor,

Try changing:

Debug.Print i & " " & aColumnWidth(i)

to

Debug.Print i & " " & aColumnWidth(i, 1)

 

[Trevor Shuttleworth]

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

 

[Bob Phillips]

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)

 

[Norman Jones]

Hi Trevor,

that works ... but why ? Is it not a one dimensional array ?

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.

 

[Norman Jones]

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.

 

[Trevor Shuttleworth]

Bob/Norman

thanks for the clarification and options. Much appreciated.

Regards

Trevor