Store range of values in an array
 

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

Trevor

Store range of values in an array
 

Hi Trevor,

Try changing:

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


to

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


---
Regards,
Norman


"Trevor Shuttleworth" wrote in message
...
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

Trevor

Store range of values in an array
 

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


"Norman Jones" wrote in message
...
Hi Trevor,

Try changing:

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


to

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


---
Regards,
Norman


"Trevor Shuttleworth" wrote in message
...
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

Trevor

Store range of values in an array
 

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)

"Trevor Shuttleworth" wrote in message
...
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

Trevor

Store range of values in an array
 

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.


---
Regards,
Norman



"Trevor Shuttleworth" wrote in message
...
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


"Norman Jones" wrote in message
...
Hi Trevor,

Try changing:

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


to

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


---
Regards,
Norman


"Trevor Shuttleworth" wrote in message
...
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

Trevor

Store range of values in an array
 

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.


---
Regards,
Norman

Store range of values in an array
 

Bob/Norman

thanks for the clarification and options. Much appreciated.

Regards

Trevor


"Bob Phillips" wrote in message
...
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)

"Trevor Shuttleworth" wrote in message
...
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

Trevor