Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to write data in array to range on worksheet
Hello Again,
And thanks, I am trying to write strings from an array to a range on a worksheet. I must be missing something. the array is dynamic and I use ReDim Preserve to load the contents. I have 6 strings in the array. I want to write the data in one column. Code snippet: Dim SheetsProcessed as Range Dim i as Integer (the array is set in a different module, and marked as Public sheetsArray() as Variant" 'displays list of sheets processed and evaluated Set SheetsProcessed = Range("K2", "K" & UBound(sheetsArray)+1) SheetsProcessed.Name = "processed" Range("processed").Value = sheetsArray ' this writes only the first array element For i = LBound(sheetsArray) To UBound(sheetsArray) Range("K" & 2 + (i + 1)).Value = sheetsArray(i) ' this writes each value in the cell Next i The line above Range("processed").value = sheetsArray places the first element in the array in 6 cells on the worksheet. If I change the line to: SheetsProcessed.Value = sheetsArray, I get the same result as the Range("processed") line above. I must be missing something here Thanks, eholz1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to write data in array to range on worksheet
On Jan 30, 9:46*am, eholz1 wrote:
Hello Again, And thanks, I am trying to write strings from an array to a range on a worksheet. I must be missing something. the array is dynamic and I use ReDim Preserve to load the contents. I have 6 strings in the array. *I want to write the data in one column. Code snippet: Dim SheetsProcessed as Range Dim i as Integer (the array is set in a different module, and marked as Public sheetsArray() as Variant" *'displays list of sheets processed and evaluated * Set SheetsProcessed = Range("K2", "K" & UBound(sheetsArray)+1) * SheetsProcessed.Name = "processed" * Range("processed").Value = sheetsArray ' this writes only the first array element * For i = LBound(sheetsArray) To UBound(sheetsArray) * * Range("K" & 2 + (i + 1)).Value = sheetsArray(i) ' this writes each value in the cell * Next i The line above Range("processed").value = sheetsArray places the first element in the array in 6 cells on the worksheet. If I change the line to: SheetsProcessed.Value = sheetsArray, I get the same result as the Range("processed") line above. I must be missing something here Thanks, eholz1 Hello All, Sorry I found the answer in THIS group by searching! range = Application.Transpose(array) But.... not sure WHY this works? Is it because it is a single column and not a range with something like: Range("A1:F6") ?? thanks again, eholz1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to write an array to a range without looping | Excel Worksheet Functions | |||
Write data inthe worksheet / array | Excel Programming | |||
Write from variant array into range | Excel Programming | |||
Write 3d array to range | Excel Programming | |||
write array to range | Excel Programming |