Write arrays in cells

[spidaero]

Hi All,

i have an array

arr(0)= 1
arr(1) = 2
arr(3) = 3
...
arr = n

and i would like to find a way so that when i write in in a cell
it gets as:

1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14
15, ....

each line will get up to 14 elements of the array with commas and then will change line until all the array elements are written.

thanks very much for your help in advance

nicholas

 

[Jon von der Heyden]

I've created a dummy 0-100 array (arr).

The idea would be loop from 1 to the # of items in the array, as follows:

Public Sub Demo()
    'Dims here
    Dim varTemp() As Variant
    Dim lngItems As Long, lngItem As Long
    Const lngN As Long = 14
 
    'rest of code
    '---dummy set-up arr---
    Dim arr(0 To 100) As Variant, i As Long
    For i = 1 To 101
        arr(i - 1) = i
    Next i
    '---

    lngItems = UBound(arr) - LBound(arr) + 1
    For lngItem = 1 To lngItems Step lngN
        varTemp = Application.Index(Application.Transpose(arr), _
                                    Evaluate("row(" & lngItem & ":" & Application.Min(lngItem + lngN - 1, lngItems) & ")"), _
                                    0)
        Cells(lngItem \ lngN + 1, 1).Value = Join$(Application.Transpose(varTemp), ";")
    Next lngItem
End Sub

 

[Jon von der Heyden]

Index doesn't care whether you have 0-based or 1-based arrays - so passing in 1 should return the first element irrespective of basing.

@FF:
I overlooked the possibility of a zero-based array and was using lower bound as starting point in the for loop, and then using loop # for row indexes. It would fail in the first iteration where it is zero, since there is no zero row.

 

[spidaero]

well running it with arr basing of arr(0 to 100) i get the following


13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26
27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40
41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54
55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68
69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82
83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96
97, 98, 99

when arr(1 to 100) i get the following:

1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14
15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28
29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42
43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56
57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70
71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84
85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98
99, 100

so i am missing some elements when the array has zero basing.

Index doesn't care whether you have 0-based or 1-based arrays - so passing in 1 should return the first element irrespective of basing.

 

[trunten]

As you don't have an answer yet I'll throw my hat back into the ring:

Private Sub writeArrayToRange(ByRef arr, ByRef rng As Range, _
                                Optional ByVal autofit As Boolean = False)
    Dim a()
    Dim i As Long, j As Long, arrayRows As Long, count As Long, n As Long
    
    arrayRows = ((UBound(arr) + 1 - LBound(arr)) / 14) + 0.5
    n = UBound(arr)
    ReDim a(1 To arrayRows, 1 To 1)
    count = LBound(arr)
    For i = 1 To arrayRows
        For j = 1 To 14
            a(i, 1) = a(i, 1) & arr(count)
            If j < 14 And count < n Then
                a(i, 1) = a(i, 1) & ", ": count = count + 1
            Else
                Exit For
            End If
        Next j
        count = count + 1
    Next i
    If autofit Then
        rng.Resize(arrayRows, 1).Value = a
    Else
        rng.Value = a
    End If
End Sub

use it like so:

Sub test()
    Dim arr(0 To 99)
        For i = 1 To 100
            arr(i - 1) = i
        Next i
    writeArrayToRange arr, [A1], True
End Sub

 

[Jon von der Heyden]

I think you may have overlooked the amendment to support zero-based arrays in post #11.

 

[trunten]

I think you may have overlooked the amendment to support zero-based arrays in post #11.

Snuck in with an edit

 

[spidaero]

Thanks everyone that reply to my thread.

I have tested all the codes you provided and they are all working fine. I just had to go with Heyden's simply because there was less lines in the implementation.

Again thanks to all for your support.

Regards,

Nicholas