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
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]
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.
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)
Dim x2(72, 1) As Variant
'x2() = sValues() 'THROWS ERROR
'Range("A1:B73") = x2
Range("A1:B73") = sValues 'Does not throw error but values are not being written
End SubRe: 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 !
Don’t have an account yet? Register yourself now and be a part of our community!