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 !


  • 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

    Range("A1:B72") = x1

    End Sub[/vba]


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


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