PDA

View Full Version : Solved: Putting String Array to cells directly



chandansify
09-11-2007, 08:08 PM
Hi All,

I just need a help on putting thousands of records from a string array to multiple cells in an excel sheet.


'Below is the example what I am doing currently to put multiple array values into cells


Sub ArraytoCells()
Dim cArray(10) As String

cArray(0) = "Value1"
cArray(1) = "Value2"
cArray(2) = "Value3"
cArray(3) = "Value4"
cArray(4) = "Value5"
cArray(5) = "Value6"
cArray(6) = "Value7"
cArray(7) = "Value8"
cArray(8) = "Value9"
cArray(9) = "Value10"

For i = 0 To 9
Cells(i + 1, 1) = cArray(i)
Next
End Sub




Can anyone suggest any better way in excel to do this task.

what i want is:


Range("A1:A10") = cArray 'i.e. putting all values in one go into excel cells.


Thanks in advance.


Regards,
Chandan

Digita
09-11-2007, 09:40 PM
For a start, try this code:


Sub ReadandWriteArray()
Dim Rng As Range
Set Rng = Range("A1:A10")
a = Rng
Range("B1:B" & Rng.Rows.Count) = a
End Sub

Regards


KP

chandansify
09-11-2007, 10:02 PM
Thanks for the reply.

But this is not what I am looking.

What I want is to put data from VB string array to a Range in excel so that I should not loop through the VB string array to put data into cells.


Range("B1:B" & Rng.Rows.Count) = a 'Here "a" should represent a VB Array. Not a range in excel.

Many thanks..
Chandan

mdmackillop
09-12-2007, 12:12 AM
Range("A1").Resize(UBound(cArray), 1) = Application.WorksheetFunction.Transpose(cArray)

Bob Phillips
09-12-2007, 12:29 AM
A slight adaptation to MD's to cater for any array start point



Range("A1").Resize(UBound(cArray) - LBound(cArray) + 1, 1) = Application.Transpose(cArray)

chandansify
09-12-2007, 12:50 AM
Thanks

mdmackillop and xld.

you all are awesome.


Many Thanks
:friends: