How to write array elements into a column

jazztech

Board Regular
Joined
Aug 31, 2007
Messages
119
Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
If someone could help:
<o:p> </o:p>
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:p> </o:p>
Many thanks in advance!
<o:p> </o:p>
Jazztech
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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!
 
Last edited:
Upvote 0
jazztech,

Having trouble understanding your code.

In the future please use code tags.


Try:

Code:
  '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
 
Last edited:
Upvote 0
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.:

Code:
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.

Code:
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
 
Upvote 0
Jazztech, Please READ the line about using CODE TAGS in PGC's signature.
The code in your post above looks just pathetic :eek:
 
Upvote 0
jazztech,

When array is declared as a numeric type, initial value is 0.
e.g
Rich (BB code):
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
Rich (BB code):
Range("a1").Resize(UBound(a)).Value = Application.Transpose(a)
<SCRIPT type=text/javascript> vbmenu_register("postmenu_1885680", true); </SCRIPT>
 
Upvote 0
PGC,

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

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top