CODEWITHSUNDEEP
excelvba
GSC
GSC

Reputation: 17

How to insert an array into excel?

I am trying to insert a range of predetermined values into excel. Here is my code:

Sub arr()

Dim arr As Variant
Set arr = [1,2,3,4,5]

    With ActiveSheet

        .Range("B1").Value = arr

    End With

End Sub

What I am trying to do is in cell B1, insert the array that I have above with the values 1 through 5. Also, if I wanted to display these values vertically, how would I go about that?

Thanks,

GSC

Upvotes: 0

Views: 557

Answers (2)

VBasic2008
VBasic2008

Reputation: 54817

Array To Range

Sub ArrayToRange()

    Const cString  As String = "1,2,3,4,5"

    Dim arr As Variant
    Dim vnt As Variant
    Dim i As Integer
    Dim cCell As String

    ' Write list to 1D Array.
    arr = Split(cString, ",")

    'arr = Array(1, 2, 3, 4, 5)

  ' 1D Array - arr

    ' Write to "A1:A5".
    cCell = "A1"
    Range(cCell).Resize(UBound(arr) + 1) = Application.Transpose(arr)

    '' Write to "B1:F1", writes values as text.
    'cCell = "B1"
    'Range(cCell).Resize(, UBound(arr) + 1) = arr

    ' Write to "B1:F1".
    cCell = "B1"
    Range(cCell).Resize(, UBound(arr) + 1) _
            = Application.Transpose(Application.Transpose(arr))

  ' 2D Array - vnt

    ' Resize 2D Array.
    ReDim vnt(1 To UBound(arr) + 1, 1 To 1)

    ' Write from 1D to 2D array.
    For i = 0 To UBound(arr)
        vnt(i + 1, 1) = arr(i)
    Next

    ' Write to "H1:H5".
    cCell = "H1"
    Range(cCell).Resize(UBound(vnt)) = vnt

    ' Write to  "I1:M1".
    cCell = "I1"
    Range(cCell).Resize(, UBound(vnt)) = Application.Transpose(vnt)

End Sub

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

You can use Join() to combine the array in a single cell.

Sub arr()

Dim arr() As Variant
Dim i As Long
arr = Array(1, 2, 3, 4, 5)

    With ActiveSheet
        .Range("B1").Value = Join(arr, ",")
        ' The loop below will start in C1, and add a single array value until it's exhausted.
        For i = LBound(arr) To UBound(arr)
            .Cells(i + 1, 3).Value = arr(i)
        Next i
    End With
End Sub

Or, for the "vertical" array, replace my For i loop with: .Cells(1,3).resize(Ubound(arr)+1).Value = Application.Transpose(arr) (Thanks @ScottCraner!)

(Thanks to @AndyG for the Join() idea)

Upvotes: 2

Related Questions