How to write array elements into a column

[jazztech]

Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
If someone could help:
<o> </o>
I have various arrays and I want to write their elements in columns (one array into one column). What is the simplest method to do it? Ideally I would set a cell by a column and a row variables and would like to display the array elements from that cell downwards.
<o> </o>
Many thanks in advance!
<o> </o>
Jazztech

 

[stanleydgromjr]

jazztech,

If "Your_Array_Name" containd 10 items, and you want to transpose the array items to, say Range("A1:A10")


You would want to use:

    Range("A1:A10") = Application.WorksheetFunction.Transpose(Your_Array_Name)

See:
http://www.mrexcel.com/forum/showthread.php?t=379017


Have a great day,
Stan

 

[jazztech]

Thank you!

 

[jazztech]

Stan,

Your suggestion works fine for one array but display zeros for another (and in the second case I can see in the 'Locals' window that the array has values in it. Maybe the problem is that this second array is a Variant type.

Any suggestion would be greatly appreciated!

Here is how I generated and displayed the first array, this works fine:

Dim BinArray() As Integer
ReDim BinArray(1 To NOE)

Dim intI As Integer

Dim BinElement As Integer
BinElement = MaxBin

For intI = 1 To NOE
BinArray(intI) = BinElement

BinElement = BinElement - BinWidth

Next
....
Dim ciBin As String
ciBin = "A"
Dim riBinF As Integer 'Row Index Bin First
riBinF = 2
Dim riBinL As Integer 'Row Index Bin Last
riBinL = riBinF + NOE

Range(ciBin & riBinF, ciBin & riBinL) = Application.WorksheetFunction.Transpose(BinArray)

and this is the code wich display zeros:

ciBin = "B"

Range(ciBin & riBinF, ciBin & riBinL) = Application.WorksheetFunction.Transpose(FreqArray)


The FreqArray was generated:

Dim FreqArray() As Variant
ReDim FreqArray(1 To NOE)

FreqArray = Application.WorksheetFunction.frequency(DataArray, BinArray)

(and I am not able to change the type from Variant to Integer somehow, that gives me a Type mismatch error...see my other post
http://www.mrexcel.com/forum/showthread.php?t=379989)

Many thanks in advance!

 

[stanleydgromjr]

jazztech,

Having trouble understanding your code.

In the future please use code tags.


Try:

  'ciBin = "A"
  'Range(ciBin & riBinF, ciBin & riBinL) = Application.WorksheetFunction.Transpose(BinArray)
  Range(ciBin & riBinF & ":" & ciBin & riBinL) = Application.WorksheetFunction.Transpose(BinArray)
  
  'or
  
  Range("A" & riBinF & ":A" & riBinL) = Application.WorksheetFunction.Transpose(BinArray)

Have a great day,
Stan

 

[pgc01]

Hi Jazztech

A remark about the Transpose method.

You don't just use the Transpose method to assign an array to a range. You use the Transpose method to change the orientation.

By default a one dimension vba array is horizontal and so you need to transpose it to assign the values to a vertical vector. You don't need it if you assign its values to a horizontal vector

Ex.:

Sub TestArray()
Dim arrData, rDest As Range
 
arrData = Array(1, 3, 23)
 
' Destination vertical, array horizontal
Range("A1:A3").Value = Application.WorksheetFunction.Transpose(arrData)
 
'  Destination horizontal, array horizontal
Range("A1:C1").Value = arrData
 
End Sub

In the case of the Frequency method, its the opposite. Frequency returns a vertical array. You should only transpose it if you want to assign its values to a horizontal vector. If you assign the result of Frequency to a vertical vector there's no need to transpose.

Sub TestFreq()
Dim arrData, arrBin, arrFreq, rDest As Range
 
arrData = Array(1, 3, 23, 12, 25, 7)
arrBin = Array(10, 20)
arrFreq = Application.WorksheetFunction.Frequency(arrData, arrBin)
 
' Remark: Frequency returns a vertical array
 
' Destination vertical, array vertical
Range("A1:A3").Value = arrFreq
 
'  Destination horizontal, array vertical
Range("A1:C1").Value = Application.WorksheetFunction.Transpose(arrFreq)
 
End Sub

 

[Stormseed]

Jazztech, Please READ the line about using CODE TAGS in PGC's signature.
The code in your post above looks just pathetic

 

[Legacy 68668]

jazztech,

When array is declared as a numeric type, initial value is 0.
e.g

Dim a(1 To 2) As Integer
a(1) = 1

Then a(2) = 0 (Initial value)
Whereas Empty for the variant type
Dump to a range for unknown size of array

Range("a1").Resize(UBound(a)).Value = Application.Transpose(a)

<SCRIPT type=text/javascript> vbmenu_register("postmenu_1885680", true); </SCRIPT>

 

[jazztech]

PGC,

Thank you! That was the problem, my code works now. I particularly appreciate the clear and detailed explanation! Great!