Can you make Range Equal All Values in an Array?

[RyanH]

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

 

[dbKemp]

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

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

 

[paul.robinson]

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

 

[Mike H]

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

 

[Peter T]

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

 

[Mike H]

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

 

[RyanH]

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

 

[RyanH]

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

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

 

[shg]

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:

 

[Peter T]

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

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