I currently fill a range with array values like this.

' store control values
For i = 1 To UBound(ctrlArray)
Sheets("Data Storage").Cells(i, lngColumn) = ctrlArray(i)
Next i

Is there a way to make this faster? I thought you could use this line
below, but it doesn't seem to work.

Sheets("Data Storage").Range(Cells(1, lngColumn), Cells(UBound(ctrlArray) +
1, lngColumn)) = ctrlArray
--
Cheers,
Ryan

On Nov 14, 8:34 am, RyanH <Ry...@discussions.microsoft.com> wrote:
> I currently fill a range with array values like this.
>
> ' store control values
> For i = 1 To UBound(ctrlArray)
> Sheets("Data Storage").Cells(i, lngColumn) = ctrlArray(i)
> Next i
>
> Is there a way to make this faster? I thought you could use this line
> below, but it doesn't seem to work.
>
> Sheets("Data Storage").Range(Cells(1, lngColumn), Cells(UBound(ctrlArray) +
> 1, lngColumn)) = ctrlArray
> --
> Cheers,
> Ryan

You need to add .value to end of asignment eg Range("A1:A5).value
You may have to use the worksheetfunction transpose.

Hi
You must make sure ctlArray has the same dimensions as the range you
are going to send it to.
Run the sub below to see ways of doing the right and wrong thing.


Sub tester()
Dim i As Integer
Dim myArray1(1 To 5, 1 To 1) As Integer
Dim myArray2(1 To 5) As Integer
Dim myVariant As Variant

For i = 1 To 5
myArray1(i, 1) = i
myArray2(i) = i
Next i
myVariant = myArray1

Range("A1:A5") = myVariant
Range("C1:C5") = myArray1
Range("E1:E5") = myArray2
Range("G1:G4") = myArray1
End Sub

regards
Paul

On Nov 14, 1:34*pm, RyanH <Ry...@discussions.microsoft.com> wrote:
> I currently fill a range with array values like this.
>
> ' store control values
> For i = 1 To UBound(ctrlArray)
> * * Sheets("Data Storage").Cells(i, lngColumn) = ctrlArray(i)
> Next i
>
> Is there a way to make this faster? *I thought you could use this line
> below, but it doesn't seem to work.
>
> Sheets("Data Storage").Range(Cells(1, lngColumn), Cells(UBound(ctrlArray)+
> 1, lngColumn)) = ctrlArray
> --
> Cheers,
> Ryan

Hi,

Id load the array like this

Sub marine()
Dim ctrlArray As Variant
ctrlArray = Application.Transpose(Range("a1:a20").Value)
For x = 1 To UBound(ctrlArray)
Debug.Print ctrlArray(x)
Next
End Sub

Mike

"RyanH" wrote:

> I currently fill a range with array values like this.
>
> ' store control values
> For i = 1 To UBound(ctrlArray)
> Sheets("Data Storage").Cells(i, lngColumn) = ctrlArray(i)
> Next i
>
> Is there a way to make this faster? I thought you could use this line
> below, but it doesn't seem to work.
>
> Sheets("Data Storage").Range(Cells(1, lngColumn), Cells(UBound(ctrlArray) +
> 1, lngColumn)) = ctrlArray
> --
> Cheers,
> Ryan

ctrlArray should be dimensioned as a 2d array

ReDim ctrlArray(1 to NumRows, 1 to 1)

Assuming LBound of each of the array's dimensions is 1, you could do
something like this

With Sheets("Data Storage").Cells(1, lngColumn)
..resize(ubound(ctrlArray), ubound(ctrlArray, 2)).value = ctrlArray
End with

Regards,
Peter T



"RyanH" <(E-Mail Removed)> wrote in message
news:A36EC79D-F491-494F-8ED7-(E-Mail Removed)...
>I currently fill a range with array values like this.
>
> ' store control values
> For i = 1 To UBound(ctrlArray)
> Sheets("Data Storage").Cells(i, lngColumn) = ctrlArray(i)
> Next i
>
> Is there a way to make this faster? I thought you could use this line
> below, but it doesn't seem to work.
>
> Sheets("Data Storage").Range(Cells(1, lngColumn), Cells(UBound(ctrlArray)
> +
> 1, lngColumn)) = ctrlArray
> --
> Cheers,
> Ryan

Hi,

I should have added that for a horizontal worksheet range the syntax is
different

ctrlArray = Application.Transpose(Application.Transpose(Range("A1:U1").Value)

Mike

"Mike H" wrote:

> Hi,
>
> Id load the array like this
>
> Sub marine()
> Dim ctrlArray As Variant
> ctrlArray = Application.Transpose(Range("a1:a20").Value)
> For x = 1 To UBound(ctrlArray)
> Debug.Print ctrlArray(x)
> Next
> End Sub
>
> Mike
>
> "RyanH" wrote:
>
> > I currently fill a range with array values like this.
> >
> > ' store control values
> > For i = 1 To UBound(ctrlArray)
> > Sheets("Data Storage").Cells(i, lngColumn) = ctrlArray(i)
> > Next i
> >
> > Is there a way to make this faster? I thought you could use this line
> > below, but it doesn't seem to work.
> >
> > Sheets("Data Storage").Range(Cells(1, lngColumn), Cells(UBound(ctrlArray) +
> > 1, lngColumn)) = ctrlArray
> > --
> > Cheers,
> > Ryan

This doesn't seem to work. I am getting an "Application-defined or
Object-defined Error"

Sheets("Data Storage").Range(Cells(1, lngColumn), Cells(UBound(ctrlArray),
lngColumn)).Value = ctrlArray

I am trying to list the control values of my userform. For example,

' get next available column number
lngColumn = 1
Do While Not IsEmpty(Sheets("Data Storage").Cells(1, lngColumn))
lngColumn = lngColumn + 1
Loop

myArray = Array("",Control1, Control2, Control3, Control4, Control5, Control6)

ERROR>> Sheets("Data Storage").Range(Cells(1, lngColumn),
Cells(UBound(ctrlArray), lngColumn)).Value = ctrlArray


Currently I use this loop to list the control values:

' store control values
For i = 1 To UBound(ctrlArray)
Sheets("Data Storage").Cells(i, lngColumn) = ctrlArray(i)
Next i
--
Cheers,
Ryan


"dbKemp" wrote:

> On Nov 14, 8:34 am, RyanH <Ry...@discussions.microsoft.com> wrote:
> > I currently fill a range with array values like this.
> >
> > ' store control values
> > For i = 1 To UBound(ctrlArray)
> > Sheets("Data Storage").Cells(i, lngColumn) = ctrlArray(i)
> > Next i
> >
> > Is there a way to make this faster? I thought you could use this line
> > below, but it doesn't seem to work.
> >
> > Sheets("Data Storage").Range(Cells(1, lngColumn), Cells(UBound(ctrlArray) +
> > 1, lngColumn)) = ctrlArray
> > --
> > Cheers,
> > Ryan
>
> You need to add .value to end of asignment eg Range("A1:A5).value
> You may have to use the worksheetfunction transpose.
>

Thanks for the replys! I used this code and didn't get any errors, but no
data was applied to the Data Storage sheet. Any ideas why?

Don't I have to use Preserve, because if I ReDim the Array it will delete
the data in the array, correct?

Why do I have to resize the column portion of .Cells(1, lngColumn) using the
Resize method? The cell is already 1 column wide, so I shouldn't have to
specify the column resized width, right?

ReDim ctrlArray(1 To UBound(ctrlArray), 1 To 1)

With Sheets("Data Storage").Cells(1, lngColumn)
.Resize(UBound(ctrlArray), UBound(ctrlArray, 2)).Value = ctrlArray
End With
--
Cheers,
Ryan


"Peter T" wrote:

> ctrlArray should be dimensioned as a 2d array
>
> ReDim ctrlArray(1 to NumRows, 1 to 1)
>
> Assuming LBound of each of the array's dimensions is 1, you could do
> something like this
>
> With Sheets("Data Storage").Cells(1, lngColumn)
> ..resize(ubound(ctrlArray), ubound(ctrlArray, 2)).value = ctrlArray
> End with
>
> Regards,
> Peter T
>
>
>
> "RyanH" <(E-Mail Removed)> wrote in message
> news:A36EC79D-F491-494F-8ED7-(E-Mail Removed)...
> >I currently fill a range with array values like this.
> >
> > ' store control values
> > For i = 1 To UBound(ctrlArray)
> > Sheets("Data Storage").Cells(i, lngColumn) = ctrlArray(i)
> > Next i
> >
> > Is there a way to make this faster? I thought you could use this line
> > below, but it doesn't seem to work.
> >
> > Sheets("Data Storage").Range(Cells(1, lngColumn), Cells(UBound(ctrlArray)
> > +
> > 1, lngColumn)) = ctrlArray
> > --
> > Cheers,
> > Ryan
>
>
>

Dimension with Redim BEFORE you start to populate the array. The point is it
should be a 2d array. Only use Preserve if you need to increase the last
dimension, ie no. of columns in this case.

If your original array needs to be 1d, eg pulled in from some other source,
try something like this

Redim tmpArr(1 to ubound(arr) - lbound(arr) + 1, 1 to 1


for i = 1 to ubound(tmpArr)
tmparr(i,1) = arr(i) ' adjust if lbound(arr) is not 1
next

then assign tmpArr to the coorectly sized range

Regards,
Peter T



"RyanH" <(E-Mail Removed)> wrote in message
news:FB56C4A3-D345-4389-87F3-(E-Mail Removed)...
> Thanks for the replys! I used this code and didn't get any errors, but no
> data was applied to the Data Storage sheet. Any ideas why?
>
> Don't I have to use Preserve, because if I ReDim the Array it will delete
> the data in the array, correct?
>
> Why do I have to resize the column portion of .Cells(1, lngColumn) using
> the
> Resize method? The cell is already 1 column wide, so I shouldn't have to
> specify the column resized width, right?
>
> ReDim ctrlArray(1 To UBound(ctrlArray), 1 To 1)
>
> With Sheets("Data Storage").Cells(1, lngColumn)
> .Resize(UBound(ctrlArray), UBound(ctrlArray, 2)).Value = ctrlArray
> End With
> --
> Cheers,
> Ryan
>
>
> "Peter T" wrote:
>
>> ctrlArray should be dimensioned as a 2d array
>>
>> ReDim ctrlArray(1 to NumRows, 1 to 1)
>>
>> Assuming LBound of each of the array's dimensions is 1, you could do
>> something like this
>>
>> With Sheets("Data Storage").Cells(1, lngColumn)
>> ..resize(ubound(ctrlArray), ubound(ctrlArray, 2)).value = ctrlArray
>> End with
>>
>> Regards,
>> Peter T
>>
>>
>>
>> "RyanH" <(E-Mail Removed)> wrote in message
>> news:A36EC79D-F491-494F-8ED7-(E-Mail Removed)...
>> >I currently fill a range with array values like this.
>> >
>> > ' store control values
>> > For i = 1 To UBound(ctrlArray)
>> > Sheets("Data Storage").Cells(i, lngColumn) = ctrlArray(i)
>> > Next i
>> >
>> > Is there a way to make this faster? I thought you could use this line
>> > below, but it doesn't seem to work.
>> >
>> > Sheets("Data Storage").Range(Cells(1, lngColumn),
>> > Cells(UBound(ctrlArray)
>> > +
>> > 1, lngColumn)) = ctrlArray
>> > --
>> > Cheers,
>> > Ryan
>>
>>
>>