# Writing an Array to a Range

#### grogs84

##### New Member
I am testing different ways to use and write arrays. I can't figure out why when I write my array to a range it give me all 0's in the first column and the corrrect number in the second. The array is not 2D so i'm not sure why it is producing 2 columns worth of data. When I use a For Loop it works like want it to. Does "range.value = array" has some tricks to it?

Sub Pct_Chg()
Dim RowNum As Long
Dim i As Long
Dim InMdlArray() As Double
Dim InTtlArray() As Double
Dim PctOffArray() As Double

RowNum = ActiveSheet.UsedRange.rows.Count - 1

ReDim InMdlArray(1 To RowNum, 1)
ReDim InTtlArray(1 To RowNum, 1)
ReDim PctOffArray(1 To RowNum, 1)

For i = 1 To RowNum
InMdlArray(i, 1) = Cells(i + 1, 5)
InTtlArray(i, 1) = Cells(i + 1, 6)
Next i

For i = 1 To RowNum
On Error Resume Next
InAcct = InTtlArray(i, 1)
InModel = InMdlArray(i, 1)
PctOffArray(i, 1) = pct_off(InAcct, InModel)
Next

Range("j2").Resize(RowNum, 2) = PctOffArray
/*** If I use .Resize(Rownum, 1) it gives me a column full of 0's ***/

For i = 1 To RowNum
Cells(i + 1, 12).Value = PctOffArray(i, 1)
Next

End Sub

Function pct_off(InAcct, InModel)

pct_off = (((InAcct) / (InModel)) - 1)

End Function

Last edited:

### Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Ruddles

##### Well-known Member
The array is not 2D so i'm not sure why it is producing 2 columns worth of data.

ReDim InMdlArray(1 To RowNum, 1)
ReDim InTtlArray(1 To RowNum, 1)
ReDim PctOffArray(1 To RowNum, 1)

All these arrays are 2D. The lower bound of the array is normally zero, so your second dimension is actually 0 To 1, i.e. two elements.

Try REDIMming the arrays as (1 To RowNum, 1 To 1) or head up your code module with an Option Base 1 directive which sets the default lower bound of an array to 1.

#### John_w

##### MrExcel MVP
As stated by Ruddles, you have declared 2D arrrays, but declaring them as (1 To RowNum, 1 To 1) will also give you 2D arrays. Try something based on this example:
Code:
``````Sub ex1()
Dim RowNum As Long
Dim i As Long
Dim PctOffArray() As Double

RowNum = 5

ReDim PctOffArray(1 To RowNum)

For i = 1 To RowNum
PctOffArray(i) = 2# * i
Next

'Convert (transpose) array from columns to rows to populate 5 rows in 1 column
Range("J2").Resize(RowNum, 1).Value = Application.Transpose(PctOffArray)

'Compare with the following which populates 5 columns in 1 row
Range("J1").Resize(1, RowNum).Value = PctOffArray
End Sub``````

#### Ruddles

##### Well-known Member
As stated by Ruddles, you have declared 2D arrrays, but declaring them as (1 To RowNum, 1 To 1) will also give you 2D arrays.
Yes, I should have omitted the second dimension. But if it's 1, doesn't that produce a 1D array?

#### mirabeau

##### Banned user

Yes, I should have omitted the second dimension. But if it's 1, doesn't that produce a 1D array?
To your first sentence No! Not necessarily.
To your second sentence, Yes!

For an array x(), ReDim x(1 to n) gives x on the worksheet as a set of items arrayed along one row. How large n can be depends on your version of Excel. If you've got lots of items and want to array them down a column, then (in this form of Dim) you have to use
Startcell.Resize = Application.Transpose(x).

However there's a limit on the size of the transpose function to 65536 even for Excel 2007 (I don't know about later versions). With more than this you may get a Runtime Error 13 or something like, if you try transposing.

To display a million or so entries of the array x down a single column you really have to use ReDim x(1 to n, 1 to 1) - or equivalent of you want to change Option Base. In this case there's no problem with Transpose limitations.

#### Ruddles

##### Well-known Member
Thanks for the helpful info.

And incidentally, using .Transpose for 50k rows is approximately 17 faster than using a loop in VBA. That's definitely one for my armoury alongside the wonderfully versatile and powerful .Resize which I only learned a few days ago!

#### mirabeau

##### Banned user

And incidentally, using .Transpose for 50k rows is approximately 17 faster than using a loop in VBA. That's definitely one for my armoury
Now I find that interesting.

Transposing 50k rows should give 50k columns, which won't show on any Excel worksheet that I've ever seen.

So presumably this comparison is entirely in your machine's memory rather on a worksheet?

#### Ruddles

##### Well-known Member
Following the example in Post #3 above:-
Code:
``Range("J2").Resize(RowNum, 1).Value = Application.Transpose(PctOffArray)``
I built an array(50000) and transposed it into Range("A1:A50000").

Last edited:

#### mirabeau

##### Banned user
And incidentally, using .Transpose for 50k rows is approximately 17 faster than using a loop in VBA. That's definitely one for my armoury ...
Hmm.

That's one I thought maybe you shouldn't be left to live with (aka How strongly do you need to be armoured?)

Try the following code on a blank worksheet.

If you look through the code I hope it's pretty self-explanatory. As set up it generates a simple, small string array and transposes it a couple of ways, one of which is the Transpose Function. The code also gives time taken for each transposition method separately, and compares.

You can next change the RowNum and/or ColNum entries near the top (to say 50k or whatever) and consider what happens with the relative timing.
Code:
``````Sub comparez()
Dim RowNum&, ColNum&, ir&, ic&, t#, time1#
Dim arry() As String, arrytr() As Variant, time2#
'****************************************
ActiveSheet.UsedRange.ClearContents
Cells.ColumnWidth = 4#
'GENERATE ARRAY
RowNum = 10   'set to whatever number you like
ColNum = 3     'set to whatever number you like
ReDim arry(1 To ColNum, 1 To RowNum)
ReDim arrytr(1 To RowNum, 1 To ColNum)
For ir = 1 To RowNum
For ic = 1 To ColNum
arry(ic, ir) = _
Chr(Int(Rnd * 26) + 65) & Chr(Int(Rnd * 26) + 97)
Next ic, ir
On Error Resume Next
Cells(1, 1).Resize(ColNum, RowNum) = arry
On Error GoTo 0
MsgBox "Array generated.  Continue ..."

'TRANSPOSE USING TRANSPOSE FUNCTION
Cells(ColNum + 2, 1) = "Transpose Fn"
Columns(1).AutoFit
t = Timer
'arrytr = Application.Transpose(arry)
'Cells(ColNum + 3, 1).Resize(RowNum, ColNum).Value = arrytr
Cells(ColNum + 3, 1).Resize(RowNum, ColNum).Value = Application.Transpose(arry)
time1 = Timer - t
MsgBox "Transpose Function took " & _
Format(time1, "0.000 secs")

'TRANSPOSE USING INDEX SWAP
Cells(ColNum + 2, ColNum + 2) = "Index Swap"
Columns(ColNum + 2).AutoFit
t = Timer
For ir = 1 To RowNum: For ic = 1 To ColNum
arrytr(ir, ic) = arry(ic, ir)
Next ic: Next ir
Cells(ColNum + 3, ColNum + 2).Resize(RowNum, ColNum) = arrytr
time2 = Timer - t
MsgBox "Index Swap took " & _
Format(time2, "0.000 secs")

'TIMING COMPARISON
If time1 > 0 Then _
MsgBox "Index Swap took " & _
Format(time2 / time1, "0.00 as long as Transpose Function")

End Sub``````

Replies
3
Views
92
Replies
3
Views
118
Replies
0
Views
94
Replies
2
Views
92
Replies
1
Views
521

1,127,209
Messages
5,623,399
Members
415,972
Latest member
SY1234

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back