Putting contents of an Array into a Cell

[John Pierce]

An array contains the elements 8, 9, 10, 15, 16
for example (no significanse to the numbers,
they are just labels). At different times the array
is repopulated with some of these same elements
and/or some others. In any case, once the array
is assembled, I want to place its entire contents
into a single cell with the appearance above.
I definitely do not want to parse the contents
one element to a cell. I'll bet this turns out to be
ridiculously simple.

 

[ImpulseBlue]

you can use the Join function to join the elements of the array with a
"," delimiter, then just return the string to the single cell

 

[Gary Keramidas]

here's some code IB gave you the method:

Sub test()
Dim arr As Variant
Dim JnArr As String
arr = Array(1, 2, 3, 4, 5)
JnArr = Join(arr, ",")
Range("A1") = JnArr
End Sub

 

[Dave Peterson]

FYI only.

Join was added in xl2k.

An array contains the elements 8, 9, 10, 15, 16
for example (no significanse to the numbers,
they are just labels). At different times the array
is repopulated with some of these same elements
and/or some others. In any case, once the array
is assembled, I want to place its entire contents
into a single cell with the appearance above.
I definitely do not want to parse the contents
one element to a cell. I'll bet this turns out to be
ridiculously simple.

 

[John Pierce]

Thank you guys, that was exactly what I needed!!!

 

[John Pierce]

Thanks Dave, I am using 2003. One more question.
Suppose that the elements of MyArray were actuall
values rather than labels. Is there a straightforward way,
similar to Join, to SUM all the elements, in this case,
58, and put that total value in a cell? Or would it take
a loop of some kind? Thanks again.

 

[Dave Peterson]

Dim myArr As Variant
myArr = Array(1, 3, 5)
MsgBox Application.Sum(myArr)

 

[Guest]

I was just getting ready to post this same question! My array is multi
dimensional though, and I'm seeing that the join function only works for
1-dimensional arrays. Below is some code I came up with, but it seems like
there might be a better way. Also, how would I take the string (with comma
delimiters) and put it back into an array?

Dim intI As Integer, intJ As Integer
Dim rng As Range
Dim BlackAArray(1 To 10, 1 To 4) As Variant

Set rng = Worksheets("Calculations").Range("BlackAMatrix")

For intI = 1 To 10
For intJ = 1 To 4
BlackAArray(intI, intJ) = rng.Cells(intI, intJ)
Next intJ
Next intI

Worksheets("Calculations").Range("M1").Value = ""

For intI = 1 To 10
For intJ = 1 To 4
If intI = 10 And intJ = 4 Then
Worksheets("Calculations").Range("M1").Value =
Worksheets("Calculations").Range("M1").Value & BlackAArray(intI, intJ)
Else
Worksheets("Calculations").Range("M1").Value =
Worksheets("Calculations").Range("M1").Value & BlackAArray(intI, intJ) & ", "
End If
Next intJ
Next intI


thanks,
Steve