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

 

[theBardd]

Activecell.Value = Join(arr, ",")

 

[Firefly2012]

Hi Nicholas

I am not very clear on what you want in each cell - would you mind providing a fuller example eg do you want Arr(0) in A1, arr(1) in B1, arr(2) in C1 etc up to arr(13) and then arr(14) in A2, arr(15) in B2 etc?

 

[JackDanIce]

Also, why do you want comma's after each number? That would change the data type from a number to a string and then you won't be able to perform calculations on it

 

[trunten]

You can use this sub to write an array to a range. Only works with 1 dimensional arrays though so be aware of that.

It takes 3 arguments, an array(strictly speaking a variant but it will fall over on anything other than an array), the range you want it to be copied to, and an optional boolean value "autofit" which will automatically resize the destination range to fit all of the numbers in the array. By default the destination range will not resize.

Have a look at my test sub to see an example.

hth

Sub test()
    Dim arr(1 To 100) As Long, i As Long
    For i = 1 To 100
        arr(i) = i
    Next i
    writeArrayToRange arr, Range("A1"), True
End Sub

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
    
    arrayRows = CLng(Application.RoundUp(UBound(arr) / 14, 0))
    ReDim a(1 To arrayRows, 1 To 14)
    count = 1
    For i = 1 To arrayRows
        For j = 1 To 14
            a(i, j) = arr(count)
            count = count + 1
            If count > UBound(arr) Then Exit For
        Next j
    Next i
    If autofit Then
        rng.Resize(arrayRows, 14).Value = a
    Else
        rng.Value = a
    End If
End Sub

 

[Jon von der Heyden]

You can use the Index WorksheetFunction and an array of row indexes to pull out segments of an array (multiple items), effectively into another array, which can then be joined to a string with a delimiter. These strings can be outputted to a range. For example, assuming you have an 1d array called 'arr':

Public Sub Demo()
    'Dims here
    Dim varTemp() As Variant
    Dim lngItem As Long
    Const lngN As Long = 14

    'rest of code
    'need to load arr somewhere here

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

 

[theBardd]

Missed the break bit

    With ActiveCell
    
        tmp = Join(arr, ",")
        start = 1
        For i = 0 To UBound(arr) Step 14
        
            On Error Resume Next
            pos = Application.Find("~", Application.Substitute(tmp, ",", "~", 15))
            On Error GoTo 0
            If Not IsError(pos) Then
                tmp1 = Mid$(tmp, pos, 99)
                .Offset(i \ 14, 0).Value = Replace(tmp, tmp1, "")
                tmp = Right$(tmp1, Len(tmp1) - 1)
            Else
                .Offset(i \ 14, 0).Value = tmp
            End If
        Next i
    End With

 

[trunten]

Needed a fix for zero based arrays (sorry):

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
    
    arrayRows = ((UBound(arr) + 1 - LBound(arr)) / 14) + 0.5
    ReDim a(1 To arrayRows, 1 To 14)
    count = LBound(arr)
    For i = 1 To arrayRows
        For j = 1 To 14
            a(i, j) = arr(count)
            count = count + 1
            If count > UBound(arr) Then Exit For
        Next j
    Next i
    If autofit Then
        rng.Resize(arrayRows, 14).Value = a
    Else
        rng.Value = a
    End If
End Sub

Edit, just realised I got totally the wrong end of the stick. Oh well...

 

[spidaero]

Hi,

how do you account for an array that is dim arr(0 to 100), starts with zero?

thanks for your support,

nicholas

You can use the Index WorksheetFunction and an array of row indexes to pull out segments of an array (multiple items), effectively into another array, which can then be joined to a string with a delimiter. These strings can be outputted to a range. For example, assuming you have an 1d array called 'arr':

Public Sub Demo()
    'Dims here
    Dim varTemp() As Variant
    Dim lngItem As Long
    Const lngN As Long = 14
 
    'rest of code
    'need to load arr somewhere here
 
    For lngItem = LBound(arr) To UBound(arr) Step lngN
        varTemp = Application.Index(Application.Transpose(arr), _
                                    Evaluate("row(" & lngItem & ":" & Application.Min(lngItem + lngN - 1, UBound(arr)) & ")"), _
                                    0)
        Cells(lngItem \ lngN + 1, 1).Value = Join$(Application.Transpose(varTemp), ";")
    Next lngItem
End Sub

 

[Firefly2012]

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.