Set Array equal to Range
 

I currently have an array of userform control values. I need to list these
controls values in a column. I currently use a For...Next Loop to list the
elements, but I would like to not use a loop. The could I have below doesn't
work, why?

Option Base 1

Sub Test()

Dim aryControls As Variant

aryControls = Array(Control1, Control2, Control3, etc.)
Sheets("Data Storage").Range(Cells(1, lngColumn), Cells(UBound(ctlArray),
lngColumn)).Value = ctlArray

End Sub
--
Cheers,
Ryan

Set Array equal to Range
 

To write an array to a range the array will have to be a 2-D array and yours
is a 1-D array.
Why worry about the For Next loop? Your array is very small, so
performance-wise it won't make a difference.

RBS


"RyanH" wrote in message
...
I currently have an array of userform control values. I need to list these
controls values in a column. I currently use a For...Next Loop to list
the
elements, but I would like to not use a loop. The could I have below
doesn't
work, why?

Option Base 1

Sub Test()

Dim aryControls As Variant

aryControls = Array(Control1, Control2, Control3, etc.)
Sheets("Data Storage").Range(Cells(1, lngColumn), Cells(UBound(ctlArray),
lngColumn)).Value = ctlArray

End Sub
--
Cheers,
Ryan

Set Array equal to Range
 

I really would like to learn how. The Loop works great, but I am trying to
learn to work with Array a little more. Is there a way to make it a
2-dimensional Array and make this code work?
--
Cheers,
Ryan


"RB Smissaert" wrote:

To write an array to a range the array will have to be a 2-D array and yours
is a 1-D array.
Why worry about the For Next loop? Your array is very small, so
performance-wise it won't make a difference.

RBS


"RyanH" wrote in message
...
I currently have an array of userform control values. I need to list these
controls values in a column. I currently use a For...Next Loop to list
the
elements, but I would like to not use a loop. The could I have below
doesn't
work, why?

Option Base 1

Sub Test()

Dim aryControls As Variant

aryControls = Array(Control1, Control2, Control3, etc.)
Sheets("Data Storage").Range(Cells(1, lngColumn), Cells(UBound(ctlArray),
lngColumn)).Value = ctlArray

End Sub
--
Cheers,
Ryan

Set Array equal to Range
 

Well, you can dim a 2-D array, loop through your 1-D array and copy to the
2-D array, but you won't gain much there.

RBS


"RyanH" wrote in message
...
I really would like to learn how. The Loop works great, but I am trying to
learn to work with Array a little more. Is there a way to make it a
2-dimensional Array and make this code work?
--
Cheers,
Ryan


"RB Smissaert" wrote:

To write an array to a range the array will have to be a 2-D array and
yours
is a 1-D array.
Why worry about the For Next loop? Your array is very small, so
performance-wise it won't make a difference.

RBS


"RyanH" wrote in message
...
I currently have an array of userform control values. I need to list
these
controls values in a column. I currently use a For...Next Loop to list
the
elements, but I would like to not use a loop. The could I have below
doesn't
work, why?

Option Base 1

Sub Test()

Dim aryControls As Variant

aryControls = Array(Control1, Control2, Control3, etc.)
Sheets("Data Storage").Range(Cells(1, lngColumn),
Cells(UBound(ctlArray),
lngColumn)).Value = ctlArray

End Sub
--
Cheers,
Ryan

Set Array equal to Range
 

So there is no way to set a Variant Variable to a 2D Array? For example,

ctrlArray = Array(Array(Control1, Control2, etc.),Array(""))
--
Cheers,
Ryan


"RB Smissaert" wrote:

Well, you can dim a 2-D array, loop through your 1-D array and copy to the
2-D array, but you won't gain much there.

RBS


"RyanH" wrote in message
...
I really would like to learn how. The Loop works great, but I am trying to
learn to work with Array a little more. Is there a way to make it a
2-dimensional Array and make this code work?
--
Cheers,
Ryan


"RB Smissaert" wrote:

To write an array to a range the array will have to be a 2-D array and
yours
is a 1-D array.
Why worry about the For Next loop? Your array is very small, so
performance-wise it won't make a difference.

RBS


"RyanH" wrote in message
...
I currently have an array of userform control values. I need to list
these
controls values in a column. I currently use a For...Next Loop to list
the
elements, but I would like to not use a loop. The could I have below
doesn't
work, why?

Option Base 1

Sub Test()

Dim aryControls As Variant

aryControls = Array(Control1, Control2, Control3, etc.)
Sheets("Data Storage").Range(Cells(1, lngColumn),
Cells(UBound(ctlArray),
lngColumn)).Value = ctlArray

End Sub
--
Cheers,
Ryan

Set Array equal to Range
 

Don't think so.

RBS


"RyanH" wrote in message
...
So there is no way to set a Variant Variable to a 2D Array? For example,

ctrlArray = Array(Array(Control1, Control2, etc.),Array(""))
--
Cheers,
Ryan


"RB Smissaert" wrote:

Well, you can dim a 2-D array, loop through your 1-D array and copy to
the
2-D array, but you won't gain much there.

RBS


"RyanH" wrote in message
...
I really would like to learn how. The Loop works great, but I am trying
to
learn to work with Array a little more. Is there a way to make it a
2-dimensional Array and make this code work?
--
Cheers,
Ryan


"RB Smissaert" wrote:

To write an array to a range the array will have to be a 2-D array and
yours
is a 1-D array.
Why worry about the For Next loop? Your array is very small, so
performance-wise it won't make a difference.

RBS


"RyanH" wrote in message
...
I currently have an array of userform control values. I need to list
these
controls values in a column. I currently use a For...Next Loop to
list
the
elements, but I would like to not use a loop. The could I have
below
doesn't
work, why?

Option Base 1

Sub Test()

Dim aryControls As Variant

aryControls = Array(Control1, Control2, Control3, etc.)
Sheets("Data Storage").Range(Cells(1, lngColumn),
Cells(UBound(ctlArray),
lngColumn)).Value = ctlArray

End Sub
--
Cheers,
Ryan

Set Array equal to Range
 

but I would like to not use a loop.

Hi. These are not controls, but does this idea help?

Sub Demo()
Dim v
v = Array(11, 12, 13)

Range("B1").Resize(3) = WorksheetFunction.Transpose(v)
End Sub

- - -
HTH
Dana DeLouis



RyanH wrote:
I currently have an array of userform control values. I need to list these
controls values in a column. I currently use a For...Next Loop to list the
elements, but I would like to not use a loop. The could I have below doesn't
work, why?

Option Base 1

Sub Test()

Dim aryControls As Variant

aryControls = Array(Control1, Control2, Control3, etc.)
Sheets("Data Storage").Range(Cells(1, lngColumn), Cells(UBound(ctlArray),
lngColumn)).Value = ctlArray

End Sub