 # Writing arrays

• Hi ..

I have an 2d array named X1(72,1). What i need to to is write the values of this array to a range of cells in an excel sheet.
I know how to achieve this by using for each loops & loop through each cell & then paste the value in cell.

Does anybody have an idea of a faster way of writing array data in cells?? I think I have seen it somewhere on the net, but cant seem to find it now :crying: ... Excel Guru's please help !

Rgds,
Tausif

• Re: Writing arrays

Try something like this.

Code
``````Dim X1(72, 1)

For i = 0 To 72
X1(i, 0) = i
X1(i, 1) = 1
Next i

Range("A1:B73") = X1``````

Boo!:yikes:

• Re: Writing arrays

Something like this[vba]Sub X()

Dim x1(1 To 72, 1 To 2) As Integer
Dim intIndex As Integer

For intIndex = 1 To 72
x1(intIndex, 1) = intIndex
x1(intIndex, 2) = intIndex * 2
Next

Range("A1:B72") = x1

End Sub[/vba]

[h4]Cheers
Andy
[/h4]

• Re: Writing arrays

Hi Again,

Thank you Norie & Andy ... Let me be a little more specific in my question. Your codes work fine .. but I am referring to the context below... In this case it doesnt seem to workk .. not sure where am i going wrong.

• Re: Writing arrays

Try this, note I have changed the output range for the function PasteExcel so you can see the result[vba]Private Function MyArray() As Integer()
Dim X1() As Integer
Dim i As Integer
ReDim X1(72, 1)
For i = 0 To 72
X1(i, 0) = i
X1(i, 1) = 1
Next i
MyArray = X1
Range("A1:B73") = X1 'Works Fine, writes to excel
PasteInExcel (MyArray)
End Function

Sub PasteInExcel(ParamArray sValues() As Variant)
Range("D1:E73") = sValues(0)
End Sub

Sub X()

Dim arrTest() As Integer

arrTest = MyArray()

End Sub[/vba]

[h4]Cheers
Andy
[/h4]

• Re: Writing arrays

Andy .... THANK YOU !! Second time you have bailed me out !! These small work arounds/tips come in extremely handy ....Thanks Again & have a gr8 day. Cheers !

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!