Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Putting contents of an Array into a Cell
Thank you guys, that was exactly what I needed!!!
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
does an array contain contents of cell | New Users to Excel | |||
#N/A if cell contents not part of an array | Excel Discussion (Misc queries) | |||
cell contents into an array | Excel Programming | |||
Putting Cell contents into a TextBox? | Excel Programming | |||
Putting Array Formula into each Cell in a Selection (that doesn'ttake eons!) | Excel Programming |