Writing an Array to a Range

grogs84

New Member
Joined
Nov 26, 2010
Messages
12
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:

Some videos you may like

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
Joined
Aug 24, 2010
Messages
5,786
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
Joined
Oct 15, 2007
Messages
6,711
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
Joined
Aug 24, 2010
Messages
5,786
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
Joined
Nov 4, 2010
Messages
2,075

ADVERTISEMENT

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(n) = 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
Joined
Aug 24, 2010
Messages
5,786
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
Joined
Nov 4, 2010
Messages
2,075

ADVERTISEMENT

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
Joined
Aug 24, 2010
Messages
5,786
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
Joined
Nov 4, 2010
Messages
2,075
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
 

Watch MrExcel Video

Forum statistics

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

Disable AdBlock

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
Top