Can you make Range Equal All Values in an Array?
 

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

Can you make Range Equal All Values in an Array?
 

On Nov 14, 8:34 am, 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

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

Can you make Range Equal All Values in an Array?
 

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 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

Can you make Range Equal All Values in an Array?
 

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

Can you make Range Equal All Values in an Array?
 

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

Can you make Range Equal All Values in an Array?
 

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

Can you make Range Equal All Values in an Array?
 

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 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.

Can you make Range Equal All Values in an Array?
 

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

Can you make Range Equal All Values in an Array?
 

I assume myArray should be ctrlArray in your example.

In any case, it's a zero-based array. In your loop, you copy values
from 1 to UBound, and that's fine. In the other method, the range must
be the same size as the array:

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


--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=29557

Can you make Range Equal All Values in an Array?
 

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