Putting contents of an Array into a Cell
 

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.

Putting contents of an Array into a Cell
 

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


John Pierce wrote:
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.

Putting contents of an Array into a Cell
 

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

--


Gary


"John Pierce" wrote in message
ups.com...
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.

Putting contents of an Array into a Cell
 

FYI only.

Join was added in xl2k.



John Pierce wrote:

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.

--

Dave Peterson

Putting contents of an Array into a Cell
 

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

Putting contents of an Array into a Cell
 

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.

Putting contents of an Array into a Cell
 

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

John Pierce wrote:

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

Putting contents of an Array into a Cell
 

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

"Dave Peterson" wrote:

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

John Pierce wrote:

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